Using a combobox to check if record exist then populate form

Anonymous
2014-09-10T22:04:07+00:00

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

Microsoft 365 and Office | Access | For home | Windows

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

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2014-09-29T16:36:04+00:00

    The mouse cursor is placed over ctrl in the line FirstFind "RecordID" = & ctrl in the VBA window, not over the control object in the form.  To then step into the code line by line press the F8 key.  You can examine the value of any variable in the same way as the code execution progresses.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2014-09-29T15:57:40+00:00

    Ken,

    I started the debugging process and placed a break on If Not IsNull(ctrl) Then. I then Opened the form view and selected a variable. I then continued to step into the code until

    FirstFind "RecordID" = & ctrl. I then placed the mouse cursor over the ctrl to see what the value is. The form seem to be frozen. Mouse over the ctrl did nothing. Any Idea?

    Kerry

    Was this answer helpful?

    0 comments
  3. Anonymous
    2014-09-25T17:36:13+00:00

    If the code you've lifted from my amended version of your stripped down file is not working correctly in your operational file I don't see that you have any alternative but to debug it.

    Open the event procedure and set a break point on the line by clicking on the left margin of the VBA window immediately alongside the line.  A marker should appear in the margin and the line should be highlighted.  Then select an item in the combo box in the usual way.  When code execution reaches the breakpoint it will stop and you can then step into the code line by line, either by means of the button on the VBA toolbar or by pressing the F8 key.  Examine the value of the ctrl variable as I described to see if it's what you'd expect.  If not you need to determine why not.  If it is as expected, but the code goes to the line which calls the MsgBox function, then you need to determine why it hasn't found the row.

    Only you can do this.  We are not there so cannot do it for you.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2014-09-25T12:54:35+00:00

    Ken,

    I am using the KerryTest51 and rebuild by form around it. This seem much easier for me than trying to debug. Debugging is new to me and not sure exactly how to go about your recommendation.

    Thanks,

    KA

    Was this answer helpful?

    0 comments
  5. Anonymous
    2014-09-23T17:19:39+00:00

    Do some debugging:

    1.  Place a breakpoint on this line:

        If Not IsNull(ctrl) Then

    2.  Select a value in the combo box and then step into the code until you get to this line:

        .FindFirst "RecordID = " & ctrl

    3.  Place the mouse cursor over ctrl and see what its value is.  it should be the RecordID value of the row in the table for the Assession Number you selected.  

    4.  Continue to step into the code.  If it goes to the line:

        MsgBox MESSAGETEXT, vbInformation, "Warning"

    it means the row hasn't been found.  You then have to work out why.

    Was this answer helpful?

    0 comments