To get Auto Number field value from table in MS Access

Question

How to get a Auto Number column value from a table in MS Access. I need the value to store it in another table. For ex,

Table1:    Table2:
Id value    CId    City
1   aaa     1      abc
2   bbb

Id in Table1 is auto numbered, where as CId in Table2 is not and note that not all values in table1 are present in table2. I'm creating a form to store value, city in respective tables. How do I get the auto number value so that I can store it as CId in table2. So, if new value is entered it would take the Id 3, I require the Id 3 to store in Table2

Also, I can't go with Form Wizard option as, I dont want all the records in the table to be visible. I just require a blank form to save new data. Please help me out in this. Thank you

Easiest approach is a form/subform arrangement. Otherwise, there are several ways to pass data between forms and a common topic. Do research and when you have specific code with issue, post question. Explore OpenArgs, TempVars, and directly setting value of control on another form. I never use form design wizard and it's use is irrelevant. You can always change form properties after it's created. If you want form only for entry of new record then set its DataEntry property to Yes. — June7
Does this answer your question? Passing parameters between forms in MS Access — June7
So, if new value is entered it would take the Id 3, I require the Id 3 to store in Table2 All new value will be stored to table2? What about INSERT INTO.... for table1 then DMAX() to get last value from auto id field and then another INSERT INTO... for table2. — Harun24HR

Harun24HR Harun24HR · Accepted Answer · 2020-07-24T02:20:03

I have used Unbound textbox Text1 to run below codes.

Private Sub cmdSQLRun_Click()
Dim MyAutoNumber As Long

    CurrentDb.Execute "INSERT INTO Table1(MyValue) VALUES('" & Me.Text1 & "')"
    'MyValue is value field of your example.
    MyAutoNumber = DMax("ID", "Table1")
    CurrentDb.Execute "INSERT INTO Table2(CID) VALUES(" & MyAutoNumber & ")"
    'CID is table2 field name.
End Sub

To get Auto Number field value from table in MS Access

1 Answers