Record primary key into variable 1

[StormcrowRahl]

I need to get the primary key for a record and place it into a variable to pass to a form. I am thinking that I should either create a module to do this when the form opens or just add the code into the Form.Open section of the form code. I just don't know how to not only find the record via vba, but how to grab just one specific column of the record and put it into a variable.

To give you a bit more understanding of why I am wanting to do this, I am using a 2 table system to essentially create session ids. When the form opens I want it to grab the currentuser and the date & time and create a record in the tblsession table. Once that record is created, I want to grab that new record's primary key and place it into a variable. Then I can use that variable to automatically fill in the control on the form to fill in the foreign key field in the main table(tblmain). I want to do this to be able to run reports for the work done during the current session by a specific user. Thanks in advance for any help.

 

[TheAceMan1]

How are ya StormcrowRahl . . .

[blue]When the form opens I want it to grab the currentuser and the date & time and create a record in the tblsession table. Once that record is created, I want to grab that new record's primary key and place it into a variable. Then I can use that variable to [purple]automatically fill in the control on the form to fill in the foreign key field in the main table[/purple](tblmain).[/blue]

Since this is a two table system its indicitive of both tables comprising the mainform. As such, with proper table relationships, a form with subform will do what you ask automatically (easily done with the [blue]Forms Wizard[/blue])!

Until your table structure is known, how to attack this outside of the Wizard remains hidden. I suggest you have a look at the following links . . . perhaps they will open your eyes! . . .

Fundamentals of Relational Database Design

Normalizing Tables

Table Relationships

See Ya! . . . . . .

 

[StormcrowRahl]

Well, I appreciate AceMan1's sage like response but I find it rather unhelpful. I am NOT a programmer, nor am I looking to become one. I am simply trying to complete a project and looking for some help in area's where the answer is eluding me. I appreciate the beginner's 101 on relational databases and table normalization but still need help on my question. And I'll grant that I might simply be missing the answer that is right in front of me. But I'll ask with the hope of getting some guidance in some form other than that of links to database fundamentals.

How, assuming I have the two tables setup correctly, and the primary key in tbl2 is a foreign key in tbl1, how I can autopopulate the foreign key field with the primary key from tbl2? I want this to be seemless to the user. Which is why I asked in the first place about using the variable. Since I will want to populate many records with the same foreign key.

 

[JoeAtWork]

AceMan's first suggestion is really the easiest method for what you want - everything is done automatically. It can be accomplished with:

1. Create a new form, for your data source use the main table.

2. In design view of the main form, from the toolbox add a subform, which should start up the wizard.

3. If you already created the subform, you can choose to use it, otherwise choose the default option (Use Existing Table or Query). If you choose the latter, the next form will let you specify which table and fields to use in the subform.

4. The next screen lets you match up the subform's foreign key to the main form's primary key. The wizard does a "best guess" but I usually like to be precise and choose it myself (i.e. the "Define my own" option).

5. If you choose "Define my own", you will get dropdowns on the left and right side. The left dropdown is for choosing the primary key of the main table, the right dropdown is for the foreign key in the subform. (ignore the dropdowns below these)

6. At this point you could click "Finish", and you should have a subform within the form, with the subform's foreign key automatically getting its value from the main form's primary key.