VBA to create a record in a table

[swenger]

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

[ranman256]

Dont use vba, use an append query..

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

[Minty]

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.

[swenger]

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

[Minty]

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.

[swenger]

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

[kd2017]

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!

[ssanfu]

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".

[swenger]

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