Displaying new record not in list

G

GuyanaSon

I have a form with a combo box. The combox box is set to select records from
a table. If a record is selected that is not in the table, a popup window
tells you that selection is not valid, then ask if you would you like to add
it.
If you select yes, a form popsup that allows you to add the record. Once the
information is added the form goes away and the information just added is
automatically selected.
Everything seems to work fine, except when the form popsup, i have to retype
the information I wish to add.
The VB code I use in this form is also used on another form in a different
database and it works fine.
 
D

Damian S

Hi GuyanaSon,

If you are using an on open event or similar to grab the text from the combo
box, it should work for you using forms!FORMNAME.COMBONAME.text

Please post your code so we can see what's happening.

Damian.
 
G

GuyanaSon

My not in list event would activate after I begin entering a record that is
not in the list, but when the form opens for me to add the new record none of
the information I had already typed is there. So I have to retype that
information again and if it is not typed the same way the record is rejected.
Here is the code.

Private Sub Checks_NotInList(NewData As String, Response As Integer)
Dim mbrResponse As VbMsgBoxResult
Dim strMsg As String

strMsg = NewData & _
"is not an existing Check." & _
" Add this new Check?"
mbrResponse = MsgBox(strMsg, _
vbYesNo + vbQuestion, "Invalid Check")
Select Case mbrResponse
Case vbYes
DoCmd.OpenForm "PreTripLabels", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=strMsg

'stop here and wait until the form
'goes away.
If Isloaded("PreTripLabels") Then
Response = acDataErrAdded
DoCmd.Close acForm, "PreTripLabels"
Else
Response = acDataErrContinue
End If
Case vbNo
Response = acDataErrContinue
End Select
End Sub
 
D

Damian S

So add an on open event to your form that inserts the text from the combo box
into the appropriate field. Use code similar to the following:

me.FIELDNAME = forms!FORMNAME.COMBONAME.text

Note: If your form is called from multiple places, you might want to use
the open arguments (parameters) to specify the form name it was called from,
this way if it's valid to open the form (for instance to manage the items in
your combo) without adding a new record, then you can check for that and not
attempt to populate the value.

Damian.
 
G

GuyanaSon

I tried that, but it did not work. I also tried the following code, but it
only allows me to update one field. I would like to open a form also add
information in the other fields.

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

Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("Check " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Check")
If intAnswer = vbYes Then
strSQL = "INSERT INTO PreTripLabels([PreTripLabels]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new check has been added to the list." _
, vbInformation, "New Check"
Response = acDataErrAdded
Else
MsgBox "Please choose a job title from the list." _
, vbInformation, "Check List"
Response = acDataErrContinue
End If
Check_NotInList_Exit:
Exit Sub
Check_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume Check_NotInList_Exit
End Sub
 
Top