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: Newest
Most helpful Newest Oldest
  1. 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
  2. Anonymous
    2014-09-23T13:33:59+00:00

    Ken, I have placed the new code into my existing database and form. All works except I get the message "No matching record". I made sure that RecordID is included in the query. Will continue to trouble shoot.

    Kerry

    Was this answer helpful?

    0 comments
  3. Anonymous
    2014-09-19T20:50:37+00:00

    Ken, You are a genius and exceptional person. You never gave up on a challenge. Thank you for your expertise and help. I hope one day I can learn as much and pay it back to someone in need. If you were here I'd take you for a pint or two.

    So far it works fine. I'll add the additional field and see if it works with out me breaking it. I'll let you know on Monday.

    Thanks again,

    Kerry

    Was this answer helpful?

    0 comments
  4. Anonymous
    2014-09-19T16:52:29+00:00

    I think we are back in business.  The root of the problem was that the bound column of the unbound combo box was, quite rightly, the primary key RecordID column, but that column was not included in the form's query.  I've added it to the query and added amended code to the various procedures in the form's module.  it should now work as expected.

    I've uploaded it as KerryTest51.accdb (I get a lot of files named Test, so I need to rename them to keep track of which is which) to my databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Let me know when you've downloaded and tested it, and I'll remove it if you are happy with it.

    Was this answer helpful?

    0 comments
  5. Anonymous
    2014-09-19T13:00:54+00:00

    I have slimmed down a simple version, placed a combobox in header and removed the calculated field I had on Full Name I used in the coboboxes for staff members. I removed the code and property values behind the Assession Number combobox in the header. I was able to save a copy in Access 2000-2003 .mdb file type. Hopefull this works. If I can get a few fields back to the form for updating, hopefully I can add to the form the rest of the fields and get them to populate as well.

    Thank you for sticking with me. I really appreciate your help.

    Kerry

    Link to One Drive: https://onedrive.live.com/redir?resid=9A4AF30CB84FCA5B!108&authkey=!AFKGgWBIBWYuGr0&ithint=file%2cmdb

    Was this answer helpful?

    0 comments