Re: Not in list combo questions

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


--

Ken Snell
<MS ACCESS MVP>

"Ernie" <Ernie@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:5753D3C9-B11E-435B-A3B3-E07F1666B3A0@xxxxxxxxxxxxxxxx
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

  • RE: jpgs not showing on forms
    ... Rather than embed the pictures in the database store the paths to the JPEG ... Private Sub cmdAddImage_Click ... Dim strAdditionalTypes As String, strFileList As String ... Private Sub cmdDeleteImage_Click ...
    (microsoft.public.access.gettingstarted)
  • Re: Newbie problem: Long list of user choices
    ... Private Sub Form_DblClick ... Private Sub VScroll1_Change ... Dim cnt As Long ... With Picture1 ...
    (comp.lang.basic.visual.misc)
  • Re: webBrowser control
    ... Dim DoNotExitWeArePrinting As Boolean ... Private Declare Sub mouse_event Lib "user32" (ByVal dwFlags As Long, ... Private Sub ScreenToAbsolute ... ' When it is simulating this click, this window MUST be the only window ...
    (microsoft.public.vb.general.discussion)
  • Re: Form behaviour when called from toolbar button
    ... Sub EditFind() ... Private Sub cmdBuiltIn_Click ... Dim hwnd As Long ... Dim ret As Long ...
    (microsoft.public.word.vba.general)
  • RichTextBox and multi-level Undo
    ... I recently needed a multi-level undo feature for my editor module, ... What I've read suggests that you need two stacks: ... Private Sub Form_Load ... Private Sub Form_Unload ...
    (microsoft.public.vb.controls)