Add 2 Records to table using one form

[nazimscr]

Dear All,

I would like to save two records to table from one form using vba. Below are the fields in my form and table that will remain same for each of the 2 records
Main Variables
TaskID (Primary Key with automatic number)
TruckNumber
DriverName
CurrentKM

for each of the below variables from the form, 2 records needs to be saved in TyreNumber field in the table


NewTyreNo
PreviousTyreNo

My form name is TaskForm and table name is Tasks

When the user clicks the save button, i would like to save two records from the same form ie one with NewTyreNo and other with PreviousTyreNo.

I am using below code to store data for the above two fields:

Dim NewTyreNo As String
Dim PreviousTyreNo As String

PreviousTyreNo= Me.PreviousTyreNo
NewTyreNo = Me.NewTyreNo

Sorry my vba knowledge is limited, i would really appreciate any help in this regard.

Regards,

Nazim

[Robeen]

Use code like this behind your Save button to Insert the two records into your Tasks table:

'I'm assuming that TyreNo is a numeric field.

Dim PrevTyre As Long, NewTyre as Long

Me.TxtPreviousTyreNo.SetFocus
PrevTyre = Me.TxtPreviousTyreNo

Me.TxtNewTyreNo.SetFocus
NewTyre = Me.TxtNewTyreNo

StrSQL = "INSERT INTO Tasks (TyreNumber) "
StrSQL = StrSQL & "VALUES (" & NewTyre & "); "
DoCmd.RunSQL StrSQL

StrSQL = "INSERT INTO Tasks (TyreNumber) "
StrSQL = StrSQL & "VALUES (" & PrevTyre & "); "
DoCmd.RunSQL StrSQL

This should insert two rows of data - each will have a new AutoNumber TaskID, and a TyreNo [either Prev or New].

Hope this helps. Let me know if there are problems.

[nazimscr]

Hi Robeen,

Thanks for the code its working well, but one issue, i slightly changed the code to incorporate other field data to the table from the form.

But one field, ie DriverName is not working which is a text field and all other fields are numeric field.

strSql = "INSERT INTO Tasks (TyreNumber,CurrentKm,TruckNumber,DriverName) "strSql = strSql & "VALUES (" & NewTyre & "," & Me.CurrentKM & "," & Me.TruckNumber & "," & Me.DriverName & "); "
DoCmd.RunSQL strSql

[Robeen]

Using Strings [Text] in one of those queries is a little more tricky because you have to pass double-quotes on either side of the string [DriverName]:
Try copying the following into your code and see if it works.

strSql = "INSERT INTO Tasks (TyreNumber,CurrentKm,TruckNumber,DriverName) "strSql = strSql & "VALUES (" & NewTyre & "," & Me.CurrentKM & "," & Me.TruckNumber & "," & "'" & Me.DriverName & "'" & "); "
DoCmd.RunSQL strSql

Also - here's a web site that belongs to pbaldy - a regular helper on this site.
There is some information there that I have found very handy.
http://www.baldyweb.com/

[nazimscr]

Thanks Robeen, its working perfectly now.

[Robeen]

I'm glad I could help!! Mark the thread solved - top of page - Thread Tools.
All the best!