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-10T22:58:23+00:00

Take a look at FindRecord.zip in my public databases folder at:

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

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

In this little demo file the form to 'go to a record' includes an unbound combo box in which the user can do any of the following:

1. Enter or select an existing contact from the list by name. This moves the form to the existing record.

2. Select <New Contact>, the first item in the list, which is included by using a UNION query as the combo box's RowSource. This moves the form to an empty new record.

3. Type in a new contact name in which case they'll be prompted whether to add the new contact to the list. If they confirm, then the form is moved to the new record and the names inserted. In my case the name entered is parsed into first and last names by the code in the combo box's NotInList event procedure, but you would not have to do this of course as your accession number is a single value, so your code would be simpler.
Was this answer helpful?

1 person found this answer helpful.

0 comments No comments
ScottGem 68,840 Reputation points Volunteer Moderator

2014-09-11T14:49:21+00:00

I use the search combo frequently. The only kicker here is that you are suing a Navigation form. I have not worked greatly with Navigation forms but as I understand them, they are an overall form with tabs, that you place embedded subforms on. So as you select a tab, you are seeing a subform on that tab. For a search combo to work, it needs to be within the same form that you want to position the record to. If its not it won't work properly.

I suspect, Ken's example uses the Not In List event to fire messages that there are no matching records.

As for ensuring that users don't "accidentally edit a record", this is a concern with Access. What I do, is use the function found here: http://allenbrowne.com/ser-56.html to lock my controls until the user presses a button to unlock them. This still allows your search controls to work (since they need to be unbound), but prevents the user from unconsciously editing data.
Was this answer helpful?

0 comments No comments
Anonymous

2014-09-11T14:31:22+00:00

Scott, thanks for the reply,

I tried this yesterday. I was able to populate the combobox with my AssessionNumber but unable to get the record into the form for editing. Any suggestions?

Kerry
Was this answer helpful?

0 comments No comments
Anonymous

2014-09-11T13:27:56+00:00

Ken, Thank you for the examples in FindRecord. I am reviewing the VB right now and have a question regarding cboGoToContact_AfterUpdate(). Please bear in mind I am fairly new at this. I am playing with the form and then reviewing the VB. How do I get the MessageText "No Matching Record" and "Warning" to fire? Can't seem to see that message with data input.

Also, Is it possible to leave the form blank until a Go To Contact has been selected. That way for my situation the users don't accidentally edit a record. Can we add a message for edit/update to ask if user really wants to change the data in a record. If yes then save edited record as update and save modified date/time.

Thank you,

Kerry
Was this answer helpful?

0 comments No comments
ScottGem 68,840 Reputation points Volunteer Moderator

2014-09-11T13:08:10+00:00

The Combobox wizard will create a search combo for you. Its the third option on the wizard.
Was this answer helpful?

0 comments No comments