Carry new autonumber from one form to the next

[TUSSFC]

tbl_Topics

* Topic_ID (autonumber)
* Topic_Name (text)

frm_Add1 has a simple 'Topic Name' field, which is entered and then a record created in tbl_Topics.

I have a little bit of VBA which validates and enters the record, closes frm_Add1 and opens frm_Add2.

On frm_Add2 I will be entering data into another table, but need to use the autonumber from tbl_Topics which was generated on frm_Add1.

How do I carry that autonumber value over from frm_Add1 to frm_Add2?

Thanks.

 

[RuralGuy]

It that is the only record you will be dealing with in frm_Add2 then why not save the current record in frm_Add1 and then put the Topic_ID in the Where clause of the OpenForm command.

 

[TUSSFC]

I'm only using the primary key from the record created on frm_Add1 to add into another table on frm_Add2.

Basically, I want people to be able to create a Topic and then create a question (which goes into tbl_Questions). The two tables are linked by the Topic_ID field.

So frm_Add1 creates the topic when the submit button is clicked and then frm_Add2 opens where the Question is created and entered into tbl_Questions. The only field in tbl_Questions I need to populate automatically is the Topic_ID from the previous form.

Does that make it any clearer?

Apologies if I've rambled more than I needed to!

 

[Pauldohert]

You could pass it in the open args parameter of the

DoCmd.OpenForm "frm_Add2", , , , , , lngTopic_ID


where lngTopic_ID is a variable that has been set = tpoic_ID

(this way it doesn't matter that you open the form2 after you have closed form1)


You can then retrive this value by using the Me.openargs in the open event of the form2

 

[TUSSFC]

That sounds perfect. I've already grabbed the Topic_ID and stored it in strTopic_ID as follows:

strTopic_ID = DMax("Topic_ID", "tbl_Topics") 'only 1 person uses the db at one time

I've used the following code to open the next form:

DoCmd.OpenForm "frm_Add2", acNormal, , , , acWindowNormal, strTopic_ID

and then put the following code on the OnLoad event for the second form:

Private Sub Form_Load()
Dim strTopic_ID As Integer
strTopic_ID = CInt(Me.OpenArgs)
End Sub

I think this is carrying the value over OK. Now how do I populate a field with the value to check?

I've tried just putting =strTopic_ID in the default value of a textfield, but just get #Name? returned.

 

[TUSSFC]

Ignore that last query. Just added this line:

Forms!frm_Add2!Topic_ID.Value = strTopic_ID

Thanks chaps.