Re: Not in list combo questions

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

"Wayne Morgan" wrote:

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.

--
Wayne Morgan
MS Access MVP


"Ernie" <Ernie@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:434B3404-9F79-4FE7-98C8-8F7BE3EC5AC3@xxxxxxxxxxxxxxxx
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!



.

Relevant Pages

  • User defined type not defined
    ... Sub SendEmail(MailServerName As String, _ ... Winsock1.SendData ("data" + vbCrLf) ... timed out while waiting for response" ...
    (comp.databases.ms-access)
  • Re: Event Log Query
    ... Dim gb_echo, gb_popup ... Sub s_initialize ... On Error Goto 0 ...
    (microsoft.public.scripting.vbscript)
  • Re: Not in list combo questions
    ... Response = acDataErrContinue ... Is there a way to populate the field in the new record form ... Private Sub cbotblvinID_NotInList ... vbCrLf & vbCrLf & _ ...
    (microsoft.public.access.forms)
  • Re: Not in list combo questions
    ... with passing the new value to the pop up form. ... Private Sub Form_Load ... Response = acDataErrContinue ... vbCrLf & vbCrLf & _ ...
    (microsoft.public.access.forms)
  • Re: alternative to Wscript.Quit for use in HTA
    ... Dim z As Integer ... Exit Sub ' Exit to avoid handler. ... strDriveLetter & VbCrLf ... understand why I just want a way to stop the script running. ...
    (microsoft.public.scripting.vbscript)