Add Recordds from Form to Table

[selavarti]

Hi,
I am having tough time to figure out how to add records to my table.
I have 3 tables
tbl_tool_type( tool_type_auto_number(Auto, PK), Tool_type)
tbl_tools( tool_type_auto_number(lookup to above table), tool_barcode, active/inactive)
There are more than one tools with same tool type but different barcodes
Now I have to add a new tool of same tool type and a new barcode.

I have form that has tool_type as combo box, a tool_barcode as text box. I select a tool_type, give a new barcode and click add record. To tbl_tools it adds a new autonumber( which not be so, it has to lookup to existing one), it adds the right auto number in the tool type instead of tool name and worst part it should not add any thing to tbl_tool_type, but it adds a new record in the tool_type(the same auto number it has in tbl_tools).

Clearly I have to add to tbl_tools not tbl_tool_type. I hope its making some sense. I am trying to do this by VBA code as command wizard is not working.

ANy one has idea how to do this.

Thanks in advance
Sudha

 

[gavinjb]

Can you post your VBA code for this, and I will look and see if I can work out what is going wrong.


Gavin,

 

[selavarti]

Private Sub Add_Click()
Dim conn As ADODB.Connection
Dim rst As New ADODB.Recordset
Set conn = CurrentProject.Connection
With rst
.Open "Select * from tbl_tools", _
conn, adOpenKeyset, adLockOptimistic
'Add a blank record
.AddNew
!tool_barcode = [Forms]![addtool]![Text2]
'Move to the first record
.MoveFirst
.Close
End With
conn.Close
Set conn = Nothing
End Sub

 

[vbajock]

.AddNew
!tool_barcode = [Forms]![addtool]![Text2]
should be followed by:
.Update
to write the changes. I don't get the .Movefirst stuff.