Not in list combo questions

[Ernie]

I am a newbe at VB and have a couple of questions I hope someone can answer.

I have a combo box that will prompt to open a form to add a new record for
an item not in the list and it seems to work fine.

Question 1. Is there a way to populate the field in the new record form with
the data previously entered in combo box? The new record form opens in
dialog which stops the code from running. It will work if I open the form in
normal mode but error msgs are created because the new data hasn't been
entered yet.

Question 2. After entering the new data and closing the add record form, I
can select the new record if I scroll to the bottom of the list. Is there
any way to have that record already selected when I return to this form from
the add new record form?

Here is the code I am using so far:


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

On Error GoTo ErrHandler

MsgBox "Please double-click this field" & vbCrLf & _
"to add a new VIN to the list.", _
vbInformation + vbOKOnly, "No Matching Record"
Response = acDataErrContinue

Exit Sub

ErrHandler:

MsgBox "Error in cbotblvinID_NotInList( ) in " & Me.Name & " form." & _
vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.description
Err.Clear

End Sub

Private Sub cbotblvinID_DblClick(Cancel As Integer)

On Error GoTo ErrHandler

Dim ntblvinID As Long

If (IsNull(Me!cbotblvinID)) Then
Me!cbotblvinID.Text = ""
Else
ntblvinID = Me!cbotblvinID
Me!cbotblvinID = Null
End If

DoCmd.OpenForm "tblvin", , , , , acDialog, "New"
Me!cbotblvinID.Requery ' Get all records.

If (ntblvinID <> 0) Then
Me!cbotblvinID = ntblvinID ' Reset to original row.
End If

Exit Sub

ErrHandler:

MsgBox "Error in cbotblvinID_DblClick( ) in " & Me.Name & " form." & _
vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.description
Err.Clear

End Sub

Private Sub Form_Load()

On Error GoTo ErrHandler

If ((Me.OpenArgs = "New") And Not IsNull(Me!tblvinID)) Then
DoCmd.GoToRecord , , acNewRec
End If

Exit Sub

ErrHandler:

MsgBox "Error in Form_Load( ) in " & Me.Name & " form." & _
vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.description
Err.Clear

End Sub

Thanks in advance for your reply!

 

[Wayne Morgan]

You're on the right track. The NotInList event is design to do all of this
though.

Instead of the message to double click to add, just use a message similar to
the following:

If MsgBox(NewData & " isn't in the list. Do you want to add it?", vbYesNo +
vbQuestion, "Not In List") = vbYes Then
DoCmd.OpenForm "tblvin", , , , , acDialog, "New," & NewData
'You can pass the NewData value in this call (as you are passing New)
'Check out the Split() function to separate these in the popup form.
'You should be able to assign this value to the appropriate control
'in the pop-up's Load event.
'Else, you can retrieve this value from the combo box in tblvin's Load
event
'You will need to refer to the correct column or, more likely, the Text
property
'of the combo box.
Response = acDataErrAdded
Else
Response = acDataErrContinue
Me.cbotblvinID.Undo
End If

The Response tells the combo box what to do after you've added the new
record and will leave you with the new record selected.

 

[Ernie]

Thanks Wayne, that fixed the displaying the record in the combo box after
adding a new record problem I was having. But I am still having a problem
with passing the new value to the pop up form.

This is the code for not in list:

If MsgBox(NewData & " isn't in the list. Do you want to add it?", vbYesNo +
vbQuestion, "Not In List") = vbYes Then
DoCmd.OpenForm "tblvin", , , , acFormAdd, acDialog, "& NewData"

And this is on the On Load event of the pop up form.

Private Sub Form_Load()

Dim NewData As String
Me!VIN = NewData

End Sub

Using the above code, the VIN field is blank when the popup opens. The
combo box is bound to the primary key of table but only the VIN field is
displayed using column widths. Does that matter? Obviously I'm missing
something ...but what is it?

Thanks, E

 

[Ken Snell \(MVP\)]

Slight changes:

If MsgBox(NewData & " isn't in the list. Do you want to add it?", vbYesNo +
vbQuestion, "Not In List") = vbYes Then
DoCmd.OpenForm "tblvin", , , , acFormAdd, acDialog, NewData

And this is on the On Load event of the pop up form.

Private Sub Form_Load()

Dim NewData As String
Me!VIN = Me.OpenArgs

End Sub

 

[Ernie]

Works great now, Thank you very much!!

Slight changes:

If MsgBox(NewData & " isn't in the list. Do you want to add it?", vbYesNo +
vbQuestion, "Not In List") = vbYes Then
DoCmd.OpenForm "tblvin", , , , acFormAdd, acDialog, NewData

And this is on the On Load event of the pop up form.

Private Sub Form_Load()

Dim NewData As String
Me!VIN = Me.OpenArgs

End Sub

 

[Wayne Morgan]

Ernie,

I see Ken got it for you. The reason I had the & in there was because you
were already passing "New" as an OpenArg so I concatenated onto what you
had. That was also the reason for using the Split() function in the pop-up's
Load event, to unconcatenate the arguments.