Adding a new record from a Combobox 1

[Griffyn]

Hi all,

I'm a bit rusty. I have a form that displays jobs, and their details. I have a Combo control that lists the existing Job Ids and navigation code that selects the appropriate record from the underlying query.

I've also got code attached to NotInList so that when a new Job ID is added, a confirmation is requested, and then the new JobID is added to the Jobs table via

Private Sub JobCombo_NotInList(NewData As String, Response As Integer)

Dim dbs As Database
Dim ClientDesc

  NewData = UCase(NewData)
  If MsgBox("That job process doesn't exist.  Create new job process?", vbYesNo) = vbYes Then
    Set dbs = CurrentDb
    dbs.Execute "INSERT INTO Jobs (Job, Client) VALUES ('" & NewData & "', '" & ClientDesc & "')", dbFailOnError
    Response = acDataErrAdded
  Else
    Response = acDataErrContinue
  End If

End Sub

This makes sure the Combo list is updated with the new job the user has entered, but the record displayed on the form is not the new job they've just added. Plus, they can't select the new job from the Combo control - it's just ignored. I presume I have to refresh the underlying query, but whatever I try leads to primary key errors. I'm trying to do a Me.Requery after the dbs.Execute in the code above.

What should I be doing?

 

[TheAceMan1]

How are ya Griffyn . . .

Post the recordsource of the form & rowsource of the combo! . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Griffyn]

Hi AceMan!

Combo RowSource:

SELECT Jobs.Job, Jobs.Client FROM Jobs ORDER BY Jobs.Job;

Form RecordSource:

SELECT Jobs.Job, Jobs.Client, Jobs.UserID, Users.User, Jobs.DateStart, Users.Email FROM Users INNER JOIN Jobs ON Users.ID = Jobs.UserID ORDER BY Jobs.Job;

 

[Griffyn]

Before I let this topic die due to it being a totally confounding, unanswerable, and wholly too difficult, I thought I'd bump it to give everyone another chance to have their say. Please do!

 

[TheAceMan1]

Griffyn! . . .

Many apologies! My email software is set to send Tek-Tips responses to its own folder. Don't ask me why yours wasn't properly policed as well as other members!

To continue . . . [blue]you can't requery the form while code in the NotInList event is executing![/blue] The form requery causes the combobox to requery. The combobox requery causes and error as the [blue]Response[/blue] arguement has yet to be realized and the combobox is in an unknown state. [purple]The NotInList event has to complete for the Response arguement to be realized and acted upon by the combobox![/purple] So without realizing it, your last post was right on the money! This isn't an easy one!

In a nutshell, what needs to be done is:
[ol][li]In the the section of code in the [blue]NotInList[/blue] event, where the user answers yes:
[ol a][li]Put the [blue]Newdata[/blue] in a [blue]private variable[/blue] for lookup after form requery.[/li]
[li]Start the forms timer. This allows the [blue]NotInList[/blue] event to complete. Thereafter when the [blue]On Timer[/blue] event triggers the form is required and we lookup the record with the new data.[/li][/ol][/li]
[li]Enter code in the [blue]On Timer[/blue] event to perform [blue]b[/blue] above.[/li]
[li]At the end of code in the [blue]On Timer[/blue] event, stop the timer![/li][/ol]
Now the code . . .
[ol][li]In the [blue]declaration section[/blue] of the form, copy/paste the following line:

[blue]Private hldData As Variant[/blue]

[/li]
[li]Copy/paste the following, replacing the code in the [blue]NotInList[/blue] event:

[blue]   Dim db As DAO.Database, ClientDesc, SQL As String
   Dim Msg As String, Style As Integer, Title As String, DL As String
   
   NewData = UCase(NewData)
   Msg = "That job process doesn't exist!" & DL & _
         "Create new job process?"
   Style = vbQuestion + vbYesNo
   Title = "User Response Required! . . ."
   
   If MsgBox(Msg, Style, Title) = vbYes Then
      Set db = CurrentDb
      SQL = "INSERT INTO Jobs (Job, Client) " & _
            "VALUES ('" & NewData & "', '" & ClientDesc & "');"
      dbs.Execute SQL, dbFailOnError
      [purple][b]hldData = NewData
      Me.TimerInterval = 5[/b][/purple]
      Response = acDataErrAdded
   Else
     Response = acDataErrContinue
   End If[/blue]

[/li]
[li]Finally, copy/paste the following to the forms [blue]On Timer[/blue] event:

[blue]   Dim Cri As String
   
   Me.Requery
   Cri = "[Job] = '" & hldData & "'"
   Me.Recordset.FindFirst Cri
   Me.TimerInterval = 0[/blue]

[/li][/ol]
[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Griffyn]

Works great. Thanks AceMan!