Link to home
Start Free TrialLog in
Avatar of joylene6
joylene6Flag for United States of America

asked on

MS Access form update question

Hello-
  I have a form.   I need the user to pick a SITE,  then have the USER field on the form update, so User can be picked.

For example, if  the NORTH LOCATION is picked, I need all the users assigned to that location to show up.


Will be using this for a report, I have been playing with the ACCESS UPDATE function in the code, I am just not doing it right.

I have a sample database attached so you can kind of see what I need.


My SECOND question is:  one this form is done, how do I connect it to a report?
FORM.docx
FORM_SAMPLE.mdb
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

The second question first: After the your form functions okay, you need to create a new report having similar recordset used in your form. Now, I am going to download the sample .mdb to handle the form.

Mike
Avatar of Dale Fye
From my iPad, so please excuse typos and inability to see your database.

Generally you will have a combo cboSites with a RowSource that looks like:  

SELECT SiteID, SiteDesc from tblSites ORDER BY SiteDesc

and then a second combo (cboUsers) with RowSource that looks like:

SELECT UserID, UserName
FROM tblUsers WHERE SiteId = Forms!yourFormName.cboSites

Then, in the AfterUpdate of cboSites, you would simply requery cboUsers with code like:

Private Sub cboSites_AfterUpdate

    me.cboUsers.Requery

End Sub

Then, because you also want cboUsers to contain the right names when you open a record which already exists, you would use the Form_Current event to also requery cboUsers, with code like:

Private Sub Form_Current

    me.cboUsers.Requery

End Sub
Basically, you want to synchronize these two combo boxes (cboSite and cboUsers) where you either start with 1) selecting a site from cboSite then you pick a user from cboUsers-- this is what you describe -- 2) or, you first select a user to force cboSite to show all relevant site/ or sites (if you happened to have more than one. brb
I will be delivering option1 as you have described. If you want option 2, then we possibly do that on a new question. It is a very powerful and useful thing to put it together (imagine you have 5 criteria like that and want to give the option to the user to start with any of the criteria without imposing hierarchy).
Which of the following column names is User Name:

SELECT 
   USERS.HELP_CODE, 
   USERS.FILE_KEY, 
   USERS.FILE_KEY_DESC, 
   USERS.Site2, 
   USERS.Site_NAME_2
FROM USERS;

Open in new window


Is it HELP_CODE?
ASKER CERTIFIED SOLUTION
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of joylene6

ASKER

I am sorry I am behind on testing this. I am without my laptop until tomorrow. I am so appreciative of the suggestions and can't wait to try them.
Hello-
    Thank you!   Ran this and looked at your code.  I think I can make the final tweaks for the form.

Regarding option 2.... If I need help hooking this to a report I will open a new question as you suggest.
Thank you for being patient with me. I normally do not have such delays after I ask a question.  your examples were very clear, I will be working with them.