adding record based on combo

[cjamps]

I am trying to add a record to a child database by inserting the serial number that the user chooses in the combo.

Private Sub Combo40_AfterUpdate()
Me.RecordsetClone.FindFirst "SerialNumber = '" & Combo40 & " '"
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

Private Sub AddTaskBtn_Click()
Dim sSQL As String
Dim dbs As Database
Set dbs = CurrentDb()
DoCmd.OpenForm "Task", , , "[TaskList.SerialNumber] =" & "'" & Me.Combo40.Column(0) & "'"
dbs.Execute "Insert Into TaskList(SerialNumber) Values ('" & Combo40 & "')"
End Sub

Once the serial number is chosen a task form opens and the user types in the task. I can't seem to get the chosen serial number into the child database. When the user exits the form, an error says that it can't save the record because the serial number is null. When I used the debug window while I ran the code line by line, the serial number did have a value.

 

[DCrake]

Suggest you change your syntax slightly from

Dim sSQL As String
Dim dbs As Database
Set dbs = CurrentDb()
DoCmd.OpenForm "Task", , , "[TaskList.SerialNumber] =" & "'" & Me.Combo40.Column(0) & "'"
dbs.Execute "Insert Into TaskList(SerialNumber) Values ('" & Combo40 & "')"

To

Dim Sn as String
Sn = Me.Combo40.Column(0)
DoCmd.SetWarnings False
DoCmd.RunSQL "Insert Into Tasklist( SerialNumber ) Select '" & Sn & "' As Expr;"
DoCmd.SetWarnings True

David

 

[cjamps]

Wow! Worked like a charm.

But where should I put the DoCmd.OpenForm "Task" statement. I have a form that the user needs to type in the task once the correct serial number is accessed from the combo box.

 

[DCrake]

After you have run the sql place

DoEvents to render the commitment before performing the next action

Then use the

DoCmd.OpenForm on the next line using the proper arguments

 

[cjamps]

I am a newbie to MS Access and I have been trying to find out information about the DOEvents() function. From what I understand it delays code from being executed until a form or report is closed. It is usually used in conjuntion with a loop. But I don't know what I am looping.

Could someone point me to a web site that would give me more information? The ms access help file was too sparse for me to understand.

Thank you for all your help.