Results 1 to 9 of 9

VBA to create a record in a table

  1. #1
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    125

    VBA to create a record in a table

    Hi,



    I have a form for table with information. For example

    fields are: ID, Name, Description, Color, Size

    I have a related Table which has ID, Test performed, where the ID is linked and there can be multiple tests per ID

    I would like to make a command button from the first form which would open up the second Form and populate the field ID to be equal to the ID that was on the first form when it opened.

    Would someone be able to give me the Code to put on the Commend button on Form 1.

    Thanks
    Sam

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    6,844
    Dont use vba, use an append query..

    docmd.openquery "qaAddRec2Table2"
    docmd.openform "frm2",,,"[id]=" & me.ID

  3. #3
    Minty is online now Hacker
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    866
    The alternative approach, and probably simpler would be to make the form a sub form and use the Link Master / Child properties.
    The subform FK ID would automatically tie up with the parent form ID.
    Please use the star below the post to say thanks if we have helped !

  4. #4
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    125
    I usually use subforms, but in this case I wanted it to pop up in a separate form window which I havent been able to see that this is possible in access through a subform

  5. #5
    Minty is online now Hacker
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    866
    You could always make the subform "appear" and "disappear" based on certain conditions to make it look as if it was a pop up?
    As always with Access many ways to skin a cat.
    Please use the star below the post to say thanks if we have helped !

  6. #6
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    125
    So I created a separate form which opens and then I used a Variable to store the PrimaryKey from the first form. The on the second form I have a Command button called new and when clicked it enters the PrimaryKey from the Variable to the Primary Key of this new form so that they can link to each other.

    However, I came across the following issue. I added to the Form_Current on the first form Variable=PrimaryKey and it tells me "Invalid Use of Null" when I open the form. However when I debug it and move my mouse over the Variable it shows the correct PrimaryKey value and when I move my mouse over the PrimaryKey it shows null

  7. #7
    kd2017 is online now Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    368
    Here's what I do: from the first form open the second form in data entry mode and supply the primary key from the first form as an open parameter.

    Then on the load event of the pop up form test if it was opened in DataEntry mode and if OpenArgs were provided. If that's true then set the default value of the foreign key's control to the OpenArgs. Else then set the default value to "".

    With this approach i don't have to first save a new record and since we're setting a controls default value instead of a field you can cancel/close the form without accidentally attempting to save empty records. (Assuming you didnt populate any other fields)

    That's just what I do and works well so far. if anyone sees any issues with this please call it out!

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    7,728
    Just an FYI.....

    fields are: ID, Name, Description, Color, Size
    "Name", "Description" & "Size" are reserved words in Access and shouldn't be used for object names. Plus they are not very descriptive.
    "Name" of what? If a persons name, you should have something link "FName", "LName".
    "Description" of what? Note that "Desc" is also a reserved word.
    "Size" of what?

    Also note that "ID" is a poor object name. It gets confusing (Access too) to have "ID" in every table.
    Have to ask: "Color" of what?

    Usually using two words (NO spaces) eliminates reserved words and is descriptive.
    Examples
    "Description" --> "EventDesc" or "EventDescription".
    "Color" -->"ItemColor" or "ColorOption".
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  9. #9
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    125
    Thank you for your comments. I am aware of all this. I was just giving an example so that readers would understand the case of what I am trying to accomplish. This is not my actual database fields

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 11-12-2017, 10:20 PM
  2. Replies: 2
    Last Post: 03-16-2017, 04:50 AM
  3. create new record in other table
    By lockstock in forum Programming
    Replies: 2
    Last Post: 01-18-2016, 09:03 AM
  4. Replies: 14
    Last Post: 12-17-2013, 01:09 PM
  5. Replies: 1
    Last Post: 11-20-2013, 09:04 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums