Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Inserting A Single Record from a form with SQL (Access 2000)

    I may be tired, but I cannot figure this out. I would like to add a single record into a table. The problem is that the form has information from 3 tables, therefore I cannot use the DoCmd. AdNewRec. The data only needs to go into one table. If I put the code in a querydef, would it be:

    INSERT INTO tablename (table column names1 , table column names2)
    FROM formname (form object names1, form object names2)

    then DoCmd openQuery ....

    I hope this makes sense.

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Inserting A Single Record from a form with SQL (Access 2000)

    What happens if you manually move to a new record in the form and type something? Do records get inserted into all three tables? If so, perhaps you should work with a form and subforms, instead of just one form.

    You can execute an SQL string in code by using

    Dim strSQL As String
    strSQL = "INSERT INTO ..." ' create appropriate SQL string here
    ' use either this instruction
    CurrentDb.Execute strSQL
    ' or this one
    DoCmd.RunSQL strSQL

    Note: if Confirm Action Queries is set to True in Tools | Options, DoCmd.RunSQL will ask the user whether it's OK to insert a record; you can avoid this by placing DoCmd.SetWarnings False before the instruction with DoCmd.RunSQL and DoCmd.SetWarnings True after it.

    Note: you will probably have to requery the form before it will "see" the new record.

  3. #3
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting A Single Record from a form with SQL (Access 2000)

    OK, I seem to have a locking problem, Basically this form is supposed to modify mixed products. My query is as follows:

    INSERT INTO MixedProductComponents ( ProductID, MixedProductID, QtyNeeded )
    VALUES ([Forms]![FRM_ModifyMixedProduct].[txtbox_ProductID], [Forms]![FRM_ModifyMixedProduct].[MixedProductID], [Forms]![FRM_ModifyMixedProduct].[QtyNeeded]);

    The error I am getting is errorcode '3008' The table 'MixedProductComponents" is already open exclusivly by another user, or it is already open though the user interface and cannot be manipulated programmatically.

    I guess my question is how can I change the form from edit mode to add mode?

    Thanks again!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Inserting A Single Record from a form with SQL (Access 2000)

    1. Are the text boxes you refer to in the SQL bound (i.e. is their control source a field in a table, more specifically a field in MixedProductComponents?

    2. Do you have the AllowAdditions property of your form set to No? If so, what happens if you set it to True? Can you enter a new record interactively then?

  5. #5
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting A Single Record from a form with SQL (Access 2000)

    The objects are unbound. Setting AllowingAdditions = true still gives the same problem. I will mess around with the form by trying to this aspect on its own form.

  6. #6
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting A Single Record from a form with SQL (Access 2000)

    Issue with my Insert statement.
    If I try to run my append querydef from the actaul querydef itself, which requires 3 values, the record will stil not save. I also have not forms open, just the query. I have the following:

    INSERT INTO MixedProductComponents ( ProductID, MixedProductID, QtyNeeded )
    SELECT [Forms]![FRM_ModifyMixedProduct].[txtbox_ProductID], [Forms]![FRM_ModifyMixedProduct].[MixedProductID], [Forms]![FRM_ModifyMixedProduct].[QtyNeeded];

    Is there anything that I would need to do to get this record to save

    Thanks Alot

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Inserting A Single Record from a form with SQL (Access 2000)

    If you don't have FRM_ModifyMixedProduct open when you run the append query, Access will see the references to the form as query parameters, so it should prompt you three times to enter values. If it doesn't (and you are really sure that the form isn't open at that time), I don't know what's happening. If it does prompt, and you enter some values, it should state that you are about to insert one record and ask if you want to continue. If you give the go-ahead, either a new record should appear in the table, or you should get an error message. If neither is the case, <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>.

    If you do have FRM_ModifyMixedProduct open when you run the append query, and appropriate values have been filled in, Access should just execute the append query, unless there is some kind of conflict (duplicate keys or missing keys or something like that). If you don't get an error message, yet no record is saved, <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29> again.

    Please describe as exactly as possible what happens when you run the append query.

  8. #8
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting A Single Record from a form with SQL (Access 2000)

    Hi Hans, I finally got it figured out. I inadvertently put a record lock on the subform. Many hours lost [img]/forums/images/smilies/sad.gif[/img]

    Thanks for your help again.

    Shimmer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •