Using a combobox to check if record exist then populate form

Anonymous

So I have posted my question to several forums and have had some good response but no resolution. I am quite new to Access 2010 and VBA. So here is my issue. I have built a Navigation Page with several forms(bound) so my users can enter data and saved to a table. What I have is a room delay form to capture information for a medical procedure and to document when a delay occurs. My TABLE is tblDelay and my form is FormDelay.

On occasion my users will enter some information and save and close the form. Then will need to edit the record. I have attempted to create a Assession Num Search in the form header based on someone's recommendation using this code:

Private Sub cboAssessionSearch_AfterUpdate()

Const MESSAGETEXT = "This Assession Number already exists."

If Not IsNull(DLookup("AssessionNumber", "tblDelay", "AssessionNumber=" & Me.AssessionNumber)) Then

MsgBox MESSAGETEXT, vbExclamation, "Invalid Operation"

'code to filter form for the existing record

Me.Filter = "AssessionNumber=" & Me.AssessionNumber

Me.FilterOn = True

Else

'code to move to new record row

DoCmd.GoToRecord , , acNewRec

End If

End Sub

I can find the populated Assession Numbers in the combobox but I need to have certain criteria. If the assession number exists the populate the form with the record fields so an edit can be made and an update to the record made in the table. If the Assession Number does not exist leave the form blank so a new record can be entered. Nothing happens when I select a Assession Number and hit enter. No error message indication record exists or not.

I have built several buttons (Add, Edit, Delate, Clear, and Close) in the header. If a assession Numbers need edited and updated then repopulate the form with the appropriate record found using the Assession Num Search combobox, If no record found, the enter new information.

Table Field Names

dept, Rm_Num, MRN, AssessionNumber, CaseNumber (Not inclusive of all fields, If I can get these filed to populate I can get the rest)

Form Names

cboDept, cboRmNum, txtMRN, txtAssessionNumber, txtCaseNumber (Not inclusive of all boxes)

Bound Form to table

Any help finding resolution would be of GREAT HELP!!!

Thank you in advance,

Kerry

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

51 answers

Anonymous

2014-09-17T16:29:51+00:00

Here is where I am at. When I input a new number into the combobox I get strMessage = "Add " & NewData & " to list?" and when selecting Yes moves the focus to the cboDept to initiated data input on a new record. Next when I select <New Record> from the combobox the focus moves to txtAssessionNumber. I will change this focus to match the other. Both of these appear to be working.

What appears not to be working is when I input a known Assession Number into the combobox I get the Warning Message: No matching record. I have in my table fields ID (Primary Key, Indexed Yes (No Duplicates)), MRN (Indexed Yes (Duplicates OK)), and AssessionNumber (Indexed Yes (No Duplicates)). I currently have this code in my cboGoToAssession event procedure:

Private Sub cboGotoAssession_AfterUpdate()

    Const MESSAGETEXT = "No matching record"

    Dim ctrl As Control

    Set ctrl = Me.ActiveControl

    If Not IsNull(ctrl) Then

        If ctrl = 0 Then

            ' go to new record and move focus to Assession control

           ' DoCmd.GoToRecord acForm, Me.Name, acNewRec (Tried but did not work)

            DoCmd.GoToRecord , , acNewRec

            Me.txtAssessionNumber.SetFocus

        Else

            With Me.RecordsetClone

                .FindFirst "ID = " & ctrl

                If Not .NoMatch Then

                    ' go to record by synchronizing bookmarks

                    Me.Bookmark = .Bookmark

                Else

                    MsgBox MESSAGETEXT, vbInformation, "Warning"

                End If

            End With

        End If

    End If

End Sub

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

    Dim ctrl As Control

    Dim strMessage As String

    Set ctrl = Me.ActiveControl

    strMessage = "Add " & NewData & " to list?"

    'if user confirms insert, move form to new record

    ' and insert values into AssessionNumber control

    If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then

        Response = acDataErrContinue

        ctrl.Undo

        DoCmd.GoToRecord , , acNewRec

        Me.cboGoToAssession = NewData

    Else

        Response = acDataErrContinue

        ctrl.Undo

    End If

End Sub

Can you see anything I have done wrong when I input a known Assession Number into the cboGoToAssession what would make a No Matching record? I have tried I think every recommendation you have made. This is the only piece not working. I have reviewed your findrecord example over and over. See no difference in what I have attempted.

Sorry for the continued questions, I am just about there.

Thanks again for all your help.

Kerry
Was this answer helpful?

0 comments No comments
Anonymous

2014-09-16T20:33:37+00:00

1. You misunderstood what I said. You can't just leave out the one argument, if you specify the object type you must name it. You can omit all bar the last argument and it will usually default to the current object, but I'd stick with what I posted, DoCmd.GoToRecord acForm,Me.Name , acNewRec

2. I didn't say a navigation page subform, I said a subform per se. The point is that a subform is not a member of the Forms collection, so referencing it with Me.Name won't work.    If you want to move to a new record in a subform with code in the parent form's module then first set focus to the subform control, and then call the GoToRecord method without specifying the form by name:

    Me.MySubformControl.SetFocus

    DoCmd.GoToRecord , , acNewRec
Was this answer helpful?

0 comments No comments
Anonymous

2014-09-16T13:00:55+00:00

I made the recommended changes including adding code to the event procedures. When I select <New Record> I get a Run-time error 2493 This action requires an Object Name argument and it highlights DoCmd.GoToRecord acForm, , acNewRec in the procedure. If I select a Assession Number from the combobox I get a message No matching record. I I type in an Assession Number I get message Add xxxx to list. If I select Yes, I get the run-time error 2493.

You mentioned yesterday that might be an issue with navigation page subform. Should I get rid of Navigation page and just use regular forms?

Thanks for all your assistance. I really appreciate experts like yourself giving of your time.

Thanks again.

Kerry
Was this answer helpful?

0 comments No comments
Anonymous

2014-09-15T23:20:42+00:00

I's really just a question of changing the relevant object names:

Private Sub cboGotoAssession_AfterUpdate()

    Const MESSAGETEXT = "No matching record"

    Dim ctrl As Control

    Set ctrl = Me.ActiveControl

    If Not IsNull(ctrl) Then

        If ctrl = 0 Then

            ' go to new record and move focus to Assession control

            DoCmd.GoToRecord acForm, Me.Name, acNewRec

            Me.Assession.SetFocus

        Else

            With Me.RecordsetClone

                .FindFirst "ID = " & ctrl

                If Not .NoMatch Then

                    ' go to record by synchronizing bookmarks

                    Me.Bookmark = .Bookmark

                Else

                    MsgBox MESSAGETEXT, vbInformation, "Warning"

                End If

            End With

        End If

    End If

End Sub

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

    Dim ctrl As Control

    Dim strMessage As String

    Set ctrl = Me.ActiveControl

    strMessage = "Add " & NewData & " to list?"

    'if user confirms insert, move form to new record

    ' and insert values into Assession control

    If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then

        Response = acDataErrContinue

        ctrl.Undo

        DoCmd.GoToRecord acForm, Me.Name, acNewRec

        Me.Assession = NewData

    Else

        Response = acDataErrContinue

        ctrl.Undo

    End If

End Sub

NB, you need to create the event procedures from the properties sheet in the usual way, and then paste in the body text of each from the above.

BTW is Assession the real name, rather than Accession?
Was this answer helpful?

0 comments No comments
Anonymous

2014-09-15T22:29:03+00:00

I am currently addressing the findrecord code you sent me. I have my combobox listing all my Assession Numbers including the <Add Record>. I have changes the name of my combobox to cboGoToAssession. So fisrt thing would be to change cboGoToContact_AfterUpdate() to cboGoToAssession_AfterUpdate(). Next I tried to change Me.FirstName.SetFocus to Me.txtMRN.SetFocus. I then changed .FindFirst "ContactID = " & ctrl to "ID = " & ctrl.

With these changes I get a message when I put a number into the combobox which states "The text you entered isn't an item in the list. Select an iten from the list, or enter text that matcheson of the listed items. Not sure where this message was derived from.

What I need to do is type in combobox an AssessionNumber, if not in list GoToAssession_NotInList. I am having trouble changing your code to meet my needs. Any help is greatly appreciated.

Thank,

Kerry

Private Sub cboGotoContact_AfterUpdate()

    Const MESSAGETEXT = "No matching record"

    Dim ctrl As Control

    Set ctrl = Me.ActiveControl

    If Not IsNull(ctrl) Then

        If ctrl = 0 Then

            ' go to new record and move focus to FirstName control

            DoCmd.GoToRecord acForm, Me.Name, acNewRec

            Me.FirstName.SetFocus

        Else

            With Me.RecordsetClone

                .FindFirst "ContactID = " & ctrl

                If Not .NoMatch Then

                    ' go to record by synchronizing bookmarks

                    Me.Bookmark = .Bookmark

                Else

                    MsgBox MESSAGETEXT, vbInformation, "Warning"

                End If

            End With

        End If

    End If

End Sub

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

    Dim ctrl As Control

    Dim strMessage As String

    Dim strFirstName As String, strLastName As String

    Set ctrl = Me.ActiveControl

    strMessage = "Add " & NewData & " to list?"

    strFirstName = Left(NewData, InStr(NewData, " ") - 1)

    strLastName = Mid(NewData, InStr(NewData, " ") + 1)

    'if user confirms insert move form to new record

    ' and insert values into first and last name controls

    If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then

        Response = acDataErrContinue

        ctrl.Undo

        DoCmd.GoToRecord acForm, Me.Name, acNewRec

        Me.FirstName = strFirstName

        Me.LastName = strLastName

    Else

        Response = acDataErrContinue

        ctrl.Undo

    End If

End Sub
Was this answer helpful?

0 comments No comments