Auto entry of data in new record

[CosmicKid]

Hi All,

Stumped on the following VB script -
I have a form (frmCircuitsMainEntry) with a combo box that access a field called "CircuitVendor". When the user enters a vendor name that isn't associated with a record, the following message is displayed - "user text" is not in the database. Do you want to add "user text" to the list?" If yes, then a seperate form (frmCompanyEntry) is opened in new record mode.
All this works, my problem is that I want the user entered data (the new vendor) the be auto entered into a field (CoName) in the new record on the opened form (frmCompanyEntry)....

Additionally, I tried suppressing the access message "blah blah not in list" to no avail with set warning to false. I think I need to suppress the message - correct? Help with the script?

Thanks in advance.

-CK

Here is the VB code so far (attached to the NotInList Event):

Private Sub CircuitVendor_NotInList(NewData As String, Response As Integer)
Dim bytResponse As Byte
bytResponse = MsgBox(CircuitVendor.Text & " is not in the database. Do you want to add " & CircuitVendor.Text & " to the list?", vbYesNo)
DoCmd.SetWarnings False
If bytResponse = vbYes Then
Me!CircuitVendor.Undo
DoCmd.OpenForm "frmCompanyEntry", acNormal, , , acFormEdit, acNormalWindow
DoCmd.GoToRecord , , acNewRec
Else
Response = acDataErrContinue
Me!LocBCompany.Undo
End If
End Sub

 

[pbaldy]

I see 2 issues offhand. I think you want to open the entry form with acDialog so that execution of this code stops and waits for the user to enter whatever data is required on it. Then you need

response = acDataErrAdded

so Access will know the data was added.

 

[CosmicKid]

hmm...still not working. I believe I need to pass the user entered data (newdata) using the openargs function, but I can't get it to work.

 

[pbaldy]

Can you post the db?

 

[CosmicKid]

After a bit of hair-pulling I manage to work out the problem. Thought I would post it for review. The problem seemed to be I was passing the NewData to the specified field of the 1st record in the table, not the new record. Works great now.

In the main form I have the following vb:

Private Sub CircuitVendor_NotInList(NewData As String, Response As Integer)
    Dim bytResponse As Byte
    bytResponse = MsgBox(CircuitVendor.Text & " is not in the database. Do you want to add " & CircuitVendor.Text & " to the list?", vbYesNo)
    If bytResponse = vbYes Then
        Me!CircuitVendor.Undo
        DoCmd.OpenForm "frmCompanyEntry", , , , acFormEdit, acNormalWindow, _
            OpenArgs:=NewData
    Else
        Response = acDataErrContinue
        Me!CircuitVendor.Undo
    End If
End Sub

In the secondary form (opened by the do.cmd in the first) I have the following code:

Private Sub Form_Load()
    If Not IsNull(Me.OpenArgs) Then
        DoCmd.GoToRecord , , acNewRec
        CoName.SetFocus
        CoName.Text = Me.OpenArgs
    End If
End Sub

Now if I can just kill the error message that pops up when the new form opens "The Text you entered isn't an item in the list." I think this is generated from the main form not the just opened form.

Thanks for the help, pbaldy!

 

[pbaldy]

As I mentioned,

response = acDataErrAdded

should clear the error. I would open the form in data entry mode so you don't have to go to a new record, and I would open it in dialog mode so code stops and waits for the user to fill out that form. Here's a procedure from one of my db's:

Private Sub cboVendorID_NotInList(NewData As String, response As Integer)
  Dim mbrResponse        As Integer
  Dim strMsg             As String
  Dim strForm            As String

  strForm = "frmPartVendorMaint"
  strMsg = NewData & " isn't in the Vendor Table.   Add as new Vendor?"
  mbrResponse = MsgBox(strMsg, vbYesNo + vbQuestion, "Invalid Vendor")
  Select Case mbrResponse
    Case vbYes
      DoCmd.OpenForm strForm, DataMode:=acFormAdd, WindowMode:=acDialog, OpenArgs:=NewData

      ' Stop here and while the popup form is completed
      If IsLoaded(strForm) Then
        response = acDataErrAdded       'add the data to the vendor combobox
        DoCmd.close acForm, strForm     'close the popup form
      Else
        response = acDataErrContinue      'error occured ... don't add the data
      End If
    Case vbNo
      response = acDataErrContinue     'user declined to add the data
  End Select
End Sub