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: Oldest
Most helpful Newest Oldest
  1. Anonymous
    2014-09-15T17:17:13+00:00

    So I am giving it an attempt to convert this over to my table and form. I started by creating an unbound combobox in my header. In the Property Sheet under Data, Row Source I have put this SQL:

    SELECT tblDelay.ID,AssessionNumber, 1 As SortColumn, AssessionNumber

    FROM tblDelay Union SELECT 0, "<New Record>", 0, "", "" FROM tblDelay

    ORDER BY SortColumn, AssessionNumber

    When I open the form and select the combobox I get the following error.

    "The number of Columns in the two selected tables or queries of a union do not match."

    How do I resolve this error.

    Thanks, Kerry

    Was this answer helpful?

    0 comments
  2. Anonymous
    2014-09-15T18:18:06+00:00

    The error is because you have an extra "", but in your case it should be this:

    SELECT ID, AssessionNumber, 1 As SortColumn

    FROM tblDelay

    UNION

    SELECT 0, "<New Record>", 0

    FROM tblDelay

    ORDER BY SortColumn, AssessionNumber;

    You don't need to repeat the AssessionNumber column in your case as it's a single column.  In my example the first and last names are repeated, once as a concatenated column for display in the list, once separately for sorting purposes.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2014-09-15T18:27:11+00:00

    Thank you for the reply Ken. I replaced with this code and get the ID in my combobox?

    Was this answer helpful?

    0 comments
  4. Anonymous
    2014-09-15T19:25:27+00:00

    The combo box's other properties should be as follows:

    BoundColumn:   1

    ColumnCount:    2

    ColumnWidths:  0cm

    If your units of measurement are imperial rather than metric Access will automatically convert the unit of the last one to inches.  The important thing is that the dimension is zero to hide the first column.

    You then search on the ID in the same way that my example searches on the ContactID column.

    Was this answer helpful?

    0 comments
  5. Anonymous
    2014-09-15T20:44:31+00:00

    GREAT!!!! That did the trick. Since my field AssessionNumber has Null values I added a WHERE clause:

    SELECT ID, AssessionNumber, 1 As SortColumn

    FROM tblDelay 

    WHERE AssessionNumber Is Not NULL

    UNION

    SELECT 0, "<New Record>", 0 FROM tblDelay ORDER BY SortColumn, AssessionNumber;

    So this identified the AssessionNumber in the combobox. I will start now to convert your CoToContact_AfterUpdate() and GoToContact_NotInList to my table and fields.

    One question to clarify inGoToContact_AfterUpdate() in the DoCmd.GoToRecord what is the Me.Name. Because of my lack of knowledge I cannot find what Me.Name does for the code.

    Thanks again,

    Kerry

    Was this answer helpful?

    0 comments