Full Version: Record not in combobox, add new record
UtterAccess Discussion Forums > Microsoft® Access > Access Forms
dmiles
Apr 16 2009, 03:45 PM
Here is a simple problem for someone programming in VBA, which is not my strength. I Have a form ASSET which has a combobox that selects the record to view, what i want to do is when I type in a barcode that is not already in the table I want it to automatically take me to add a new record. I am sure this is as simple as just modifiying the code in the IF then statement, however I do not have enough experiance to get the verbage correct.

CODE
Private Sub Combo38_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Asset_Identification] = " & Str(Nz(Me![Combo38], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub


Any one
SerranoG
Apr 16 2009, 03:55 PM
How about?

CODE
Private Sub Combo38_AfterUpdate()
    
    ' Find the record that matches the control.
    Dim rs As Object
    
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Asset_Identification] = '" & Str(Nz(Me![Combo38], 0)) & "'"
    
    If rs.EOF Then
        DoCmd.GoToRecord , , acNewRec
    Else
        Me.Bookmark = rs.Bookmark
    End If
End Sub

Untested!
dmiles
Apr 16 2009, 04:11 PM
Tried that, no dice... suggestions?
Jack Cowley
Apr 16 2009, 04:33 PM
Use the Combos Not In List event... This article shows you two methods...

hth,
Jack
pere_de_chipstick
Apr 16 2009, 04:37 PM
You could try this in the Combo box's not in list event:
CODE
    DoCmd.RunCommand acCmdUndo
    DoCmd.GoToRecord , , acNewRec
    Response = acDataErrContinue


or use this Not In List code in the code archives

HTH
dmiles
Apr 16 2009, 05:27 PM
That worked!! Thank you, I was on the right path, but not being proficient with code yet it might have taken me 3 or 4 days...lol,, but I am learning.

Nice to know I have this great big resource, someday I will be on the other end of the question..
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.