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 No comments

51 answers

Sort by: Newest
  1. Anonymous
    2014-09-18T18:49:52+00:00

    Sorry, must have brain damage from this project. Here is the link forgot in last post.

    https://onedrive.live.com/redir?resid=9A4AF30CB84FCA5B!106&authkey=!AChlsuCaZs5ztZc&ithint=file%2caccdb

    Was this answer helpful?

    0 comments No comments
  2. Anonymous
    2014-09-18T18:10:54+00:00

    Here is a new link to OneDrive. I 've creased a slimmed down version called Test. I have only included a few txt boxes, and several cascading combo box. I appears to post some data to the form bu I get a new run time error 3070.

    Was this answer helpful?

    0 comments No comments
  3. Anonymous
    2014-09-18T17:53:01+00:00

    So I am creating a new smaller dB without Navigation pages just for testing. Hope to see if I can get the search button working before developing the rest of the form.

    Was this answer helpful?

    0 comments No comments
  4. Anonymous
    2014-09-18T15:55:28+00:00

    I'm not sure what you havew in mind by 'reformat', but the problem is that your form apparently includes features which are not supported in Access 2007.  What those features are I can't say of course without being able to open the form.  Catch 22!

    Was this answer helpful?

    0 comments No comments
  5. Anonymous
    2014-09-18T15:41:33+00:00

    I'll try to reformat for you. Because I split the dB on our company share drive I had to redirect the front end to the new location of the back end. My experience was I can open front-end but clicking on FormDelay I had a black screen. Once I redirected the front-end to new location of back end I was able to see form. I'll see what changes I can make.

    Sorry,

    KA

    Was this answer helpful?

    0 comments No comments