joylene6
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
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
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.cboSite s
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
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.cboSite
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:
Is it HELP_CODE?
SELECT
USERS.HELP_CODE,
USERS.FILE_KEY,
USERS.FILE_KEY_DESC,
USERS.Site2,
USERS.Site_NAME_2
FROM USERS;
Is it HELP_CODE?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
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! 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.
ASKER
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.
Mike