By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,164 Members | 1,001 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,164 IT Pros & Developers. It's quick & easy.

Navigating through Forms, Based on controls.

P: 99
Hi,

I am trying to set up the forms for my DB. I have the table structure and relationships set up properly. I have an issue with 2 of the tables: tblClientInformation and tblCompanyType. The form for tblClientInformation allows you to choose company type from a drop down list of 5 options (A-E) which is linked to tblCompanyType. After entering basic information on this form, people will click the “continue” button to go to the next form. I need 5 different forms here. 1 for each of the selections A-E. I need the continue button to know to go to the form with the corresponding information for A-E. (A-E have different information). 1 client can only go to 1 of the 5 forms. So if you select C from the drop down, you go to form “C” for further entry. After entering information on 1 of the 5 forms, they will continue on and all end up back at the same (new) form. This new form, will have a back button which will allow the same action as what I am trying to do for the continue button. If anyone has any suggestions I would appreciate it. I am working in Access 2000 on Windows.
Dec 5 '06 #1
Share this Question
Share on Google+
25 Replies


MSeda
Expert 100+
P: 159
Just use a case select statement in the continue buttons click event, like this

Private Sub Continue_Click()

Select Case Me.CompanyType

Case "A"
Docmd.openform "FormA", , , "[ClientID] = me.ClientID"

Case "B"
Docmd.openform "FormA", , , "[ClientID] = me.ClientID"

etc...
End Select
Dec 5 '06 #2

MSeda
Expert 100+
P: 159
the form opened in case "B" should read "Form B". sorry for the typo
Dec 5 '06 #3

NeoPa
Expert Mod 15k+
P: 31,660
You could have the Type ComboBox use two columns. One for the display and selection by the operator and another hidden one with the form name required for that type. When they click on Continue, you open the form returned by the ComboBox.
The Back button is less straightforward. You would need for the code to 'Remember' or keep access to the original selection. There are a number of ways to do this depending on your requirements. Possibly the easiest is to pass it from form to form in the OpenArgs parameter.
Dec 5 '06 #4

P: 99
Ok. This is a little more complicated now. I looked over my forms. THe A-E choice is on the 1st form. I then have 1 more form, which everyone will go to. THen I need the "continue" button from that form to the next to have the 5 choices on where to go (but not have the selection of A-E displayed on this form. So any thoughts on how to base this form button off of an answer from the table the form is based off of, but which does not display the choice on the form. Thanks.
Dec 8 '06 #5

NeoPa
Expert Mod 15k+
P: 31,660
Ok. This is a little more complicated now. I looked over my forms. THe A-E choice is on the 1st form. I then have 1 more form, which everyone will go to. THen I need the "continue" button from that form to the next to have the 5 choices on where to go (but not have the selection of A-E displayed on this form. So any thoughts on how to base this form button off of an answer from the table the form is based off of, but which does not display the choice on the form. Thanks.
Does my earlier post not answer this question?
Let me know if you want further clarification.
Dec 8 '06 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
Ok. This is a little more complicated now. I looked over my forms. THe A-E choice is on the 1st form. I then have 1 more form, which everyone will go to. THen I need the "continue" button from that form to the next to have the 5 choices on where to go (but not have the selection of A-E displayed on this form. So any thoughts on how to base this form button off of an answer from the table the form is based off of, but which does not display the choice on the form. Thanks.
You need to pass the value into a global variable which can then be used later in the SELECT CASE statement as NeoPa suggested.

To create this variable you need to declare it in a module.

Expand|Select|Wrap|Line Numbers
  1. GLOBAL coType As String
Then in the first form pass the value into the global variable in the after update event of the combobox.

Expand|Select|Wrap|Line Numbers
  1. coType = comboboxname
Then for the SELECT CASE statement.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Select Case coType
  3.  
  4. Case "A"
  5. Docmd.openform "FormA", , , "[ClientID] = me.ClientID"
  6.  
  7. Case "B"
  8. Docmd.openform "FormB", , , "[ClientID] = me.ClientID"
  9.  
Mary
Dec 9 '06 #7

P: 99
AWESOME! Thanks ya'll. Sorry it took me a while to get back here and try this, but it works great. 2 Follow-up questions.

1st. As it stands now, when I click on the "continue" button a message pops up saying: Enter Parameter Vale, me.ClientID. Now I can simply type in an ID number and it takes it to the correct form, but is it possible to get it to realize what the current client id is showing?

2nd. Is it possible to set the forms to disallow the clientID for forms not selected from the dropdown box? Since I have all my forms based off a single table (tblClients), these forms (A-E) show all the clients. Can I somehow change these 5 forms to not show the client information for any client with a different selection? So, you select A on form 1, and you click the cool button which opens from A; but if I were to manually open form B, clients that had been sent to From A will also have information available to be entered. Is it possible to correct this? Thanks again, I really appreciate all ya’lls hard work.
Dec 11 '06 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
AWESOME! Thanks ya'll. Sorry it took me a while to get back here and try this, but it works great. 2 Follow-up questions.

1st. As it stands now, when I click on the "continue" button a message pops up saying: Enter Parameter Vale, me.ClientID. Now I can simply type in an ID number and it takes it to the correct form, but is it possible to get it to realize what the current client id is showing?
Pass it to another global variable and change Me.ClientID to the global variable name.

2nd. Is it possible to set the forms to disallow the clientID for forms not selected from the dropdown box? Since I have all my forms based off a single table (tblClients), these forms (A-E) show all the clients. Can I somehow change these 5 forms to not show the client information for any client with a different selection? So, you select A on form 1, and you click the cool button which opens from A; but if I were to manually open form B, clients that had been sent to From A will also have information available to be entered. Is it possible to correct this? Thanks again, I really appreciate all ya’lls hard work.
Don't quite understand the question. Sorry Robert

Mary
Dec 12 '06 #9

P: 99
Pass it to another global variable and change Me.ClientID to the global variable name.
Part 1:

With this inserted code, the button no longer works. Any thoughts on what I did wrong with the code? Here’s the VBA code behind the button in the form:

Private Sub Command56_Click()
Select Case coType
Select Case coClient

Case "A"
DoCmd.OpenForm "frmTotalRevenue1120", , , "[ClientAutoID] = CoClient"

Case "B"
DoCmd.OpenForm "frmTotalRevenue1040", , , "[ClientAutoID] = CoClient"

End Select
End Select
End Sub

CoClient is a new module I created (Code):

Option Compare Database

Global coClient As String

Don't quite understand the question. Sorry Robert

Mary
Part 2: So all my forms are based off of 2 tables. The sbf’s are based off of tblAnswers which is appended through a query. The other forms are based off of tblClient. This allows for all clients to be shown on each form, and the sbf’s with matching ClientID’s to be displayed. Because of this setup, my 5 forms (A-E) each contain all clients. So, even though I select case A above for a client; that client will still have information on forms B-E. I wasn’t sure if I could set the forms to restrict the clients shown to only those with the corresponding Letter. So all clients which have case B selected will only have information on frmTotalReveneue1040 (Form B). I thought that this would be a good safety feature incase someone opened the forms without going through the “continue” button as described above.
Dec 12 '06 #10

MSeda
Expert 100+
P: 159
The reason you are being asked for a paramater is because of the syntax in your where statement of OpenForm.
In my previous post I had dropped all of the quotes from the where statements in my suggestion since I didn't know whether your client ID was numeric or text or how you planned on referencing it.
try changing all of the where statements to match the following.
DoCmd.OpenForm "frmTotalRevenue1120", , , "[ClientAutoID] = " & me.Client
you would also use the same syntax with a global variable.

change the case select back to it's previous version.

If you want to use a global variable to 'remeber' the clientID just use the statement coClient = me.client to set the variable to the current client prior to referencing it.

the following site has good info and examples for using globals:
http://www.blueclaw-db.com/access_da...l_variable.htm
Dec 12 '06 #11

P: 99
The reason you are being asked for a paramater is because of the syntax in your where statement of OpenForm.
In my previous post I had dropped all of the quotes from the where statements in my suggestion since I didn't know whether your client ID was numeric or text or how you planned on referencing it.
try changing all of the where statements to match the following.
DoCmd.OpenForm "frmTotalRevenue1120", , , "[ClientAutoID] = " & me.Client
you would also use the same syntax with a global variable.

change the case select back to it's previous version.

If you want to use a global variable to 'remeber' the clientID just use the statement coClient = me.client to set the variable to the current client prior to referencing it.

the following site has good info and examples for using globals:
http://www.blueclaw-db.com/access_da...l_variable.htm
Ok. Still no luck. Here’s what I have now:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command56_Click()
  2. Select Case coType
  3.  
  4. Case "Corporation"
  5. DoCmd.OpenForm "frmTotalRevenue1120", , , "[ClientAutoID] = Me.ClientAutoID"
  6. WHERE DoCmd.OpenForm "frmTotalRevenue1120", , ,"[ClientAutoID] = " & me.Client
  7.  
  8. End Select
  9. End Sub
An error message pops up after the second “frmTotalRevenue1120” and says “expected end of statement”. ClientAutoId is a numeric number.
Dec 12 '06 #12

NeoPa
Expert Mod 15k+
P: 31,660
Ok. Still no luck. Here’s what I have now:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command56_Click()
  2. Select Case coType
  3.  
  4. Case "Corporation"
  5. DoCmd.OpenForm "frmTotalRevenue1120", , , "[ClientAutoID] = Me.ClientAutoID"
  6. WHERE DoCmd.OpenForm "frmTotalRevenue1120", , ,"[ClientAutoID] = " & me.Client
  7.  
  8. End Select
  9. End Sub
An error message pops up after the second “frmTotalRevenue1120” and says “expected end of statement”. ClientAutoId is a numeric number.
That's because you have a great big WHERE at the front of the line. This is not right.
Dec 12 '06 #13

MSeda
Expert 100+
P: 159
I'm sorry I should have been clearer in my post. When I said to change the where statement I was refering to the 4th part of the openForm statement not an additional statement.

Try this:
Private Sub Command56_Click()

coType = me.combobox

Select Case coType

Case "A"
DoCmd.OpenForm "frmTotalRevenue1120", , , "[ClientAutoID] = " & Me.ClientAutoID

Case “B”
DoCmd.OpenForm "frmTotalRevenue1040 , "[ClientAutoID] = " & Me.ClientAutoID

Etc…
End Select
End Sub

The “"[ClientAutoID] = " & Me.ClientAutoID” is the where statement
Use the same

It appears that you were attempting to set the value of the client global variable at the begging of the case select with the second “Select Case coClient” and that’s when the button stopped working

Let me also go into a little more detail about the Case Select statement.
On the form where the user selects A-E to and then progresses to one of five subsequent forms the case select statement was suggested to serve as, well, a fancy if statement.

Case Selects operate almost exactly like Ifs except you have results for more than just true or false.

Select Case What you Want to Evaluate(Expr)
Case the first thing it might be equal to(A)
What to do
Case the second thing it might be equal to(B)
What to do
End Select

The following set of if statements would yield the same result

If Expr = A Then
Do A
Else
If Expr = B then
Do B
End if
End if

All a case select does is compare the statement after the words “Select Case” to each statement following the word “Case” and performs the block of code where the statements are equal.
So, you see the second “Select Case coClient” was then being evaluated against A, B and C instead of coType so none of the cases would be true, hence the button stopped working.

That being said
It was suggested you use a global variable to track what the user entered in A-E and or the clientID.
The reason this was suggested is a global variable can be used to “remember” the choice from the first form even after that form is closed so say if the third form displayed is dependent on the A-E option from form 1 you can either dlookup back to you data table to see if that client is an A or an E but or you can refer to your previously stored variable.
Personaly I find it to be convenient to just refer to a variable especially if a process involves a lot of forms and user selections determining which form opens next.

In order to use a global variable you must first declare it, in a module is best, as it appears you have done.
You must then set its value.
You can do this in the after update event of a control or with the click of a command button (any event really as long as it makes sense).
Lets say the user uses your combobox to choose option C

In the after update event you would enter

GloOpt = me.combobox

That will store the value of combobox in GloOpt

You can now refer to GloOpt any time (until you close the program, have a runtime error, or reset it elsewhere) to recall the option selected in combobox.

I’m going to ignore the second question in post #8 it would probably be better to post it in a new thread.
Dec 12 '06 #14

P: 99
Ok. First off, I tried the code and that works great now. Thanks for helping someone with no code knowledge. Second:


That being said
It was suggested you use a global variable to track what the user entered in A-E and or the clientID.
The reason this was suggested is a global variable can be used to “remember” the choice from the first form even after that form is closed so say if the third form displayed is dependent on the A-E option from form 1 you can either dlookup back to you data table to see if that client is an A or an E but or you can refer to your previously stored variable.
Personaly I find it to be convenient to just refer to a variable especially if a process involves a lot of forms and user selections determining which form opens next.

In order to use a global variable you must first declare it, in a module is best, as it appears you have done.
You must then set its value.
You can do this in the after update event of a control or with the click of a command button (any event really as long as it makes sense).
Lets say the user uses your combobox to choose option C

In the after update event you would enter

GloOpt = me.combobox

That will store the value of combobox in GloOpt

You can now refer to GloOpt any time (until you close the program, have a runtime error, or reset it elsewhere) to recall the option selected in combobox.

I’m going to ignore the second question in post #8 it would probably be better to post it in a new thread.
I am going to try to add the global variable (and replace the code?). I do have a module set up thanks to “all of the above”. It is simply: (md1)

Option Compare Database
Global coType As String

I copied my form and deleted the code to try this:

Private Sub ClientCompanyType_AfterUpdate()
GloOpt = Me.ClientCompanyType
End Sub

I imagine that I need to rename GloOpt to something else? But other than that I don’t know VBA so I’m not sure what to do. So any more suggestions are appreciated. Thanks ya’ll for the help with this stuff (and gratz to Mary on the 2k posts). (At least 50 of those were probably me)
Dec 12 '06 #15

P: 99
Part 2 of Question 8 is now at: Excluding Clients from specific Forms - thread.
Dec 12 '06 #16

MSeda
Expert 100+
P: 159
First, the use of the global variable in no way replaces the Select Case statement.

The Select statement compares the users input to a list of possible options and chooses which form to open.

The Global variable remembers the users input so that if you need to reference it once you have closed the form it was originally entered on with out having to perform a query or dlookup on the table where it is stored or it could be something not stored in a table that only needs to be remembered temporarily.

You are already using the global variable in your select case if I am reading your posts correctly.

as mmcarthy suggested you have entered in the combos after update
Private Sub combo1_AfterUpdate()

coType = me.combo1

End Sub

And your select case then refers to is
If coType had not been set by the after update event of the combo box then your select case would read
Select Case me.combo1
Dec 12 '06 #17

NeoPa
Expert Mod 15k+
P: 31,660
MSeda,

Check out the other thread (linked now) as I think it changes the question somewhat.
Dec 12 '06 #18

P: 99
MSeda,

Check out the other thread (linked now) as I think it changes the question somewhat.
How do you link them? I will do that from now on when I reference another thread.
Dec 12 '06 #19

NeoPa
Expert Mod 15k+
P: 31,660
I generally type out the url tags but if you post the http:// link it will happily ignore you until you edit the thread. Then it will convert it to a basic link.
The format for a link with a display name is (I've replaced [] with {} so that it will leave it and not display a link) :
{url=http://ActualLink}Display Title{/url}.
To get the link and Title data I link to the page itself and copy them via the clipboard.
Dec 12 '06 #20

P: 99
I generally type out the url tags but if you post the http:// link it will happily ignore you until you edit the thread. Then it will convert it to a basic link.
The format for a link with a display name is (I've replaced [] with {} so that it will leave it and not display a link) :
{url=http://ActualLink}Display Title{/url}.
To get the link and Title data I link to the page itself and copy them via the clipboard.
Cool. I'll start doing that.
Dec 12 '06 #21

P: 99
Cool. I'll start doing that.
Maybe I should post a thread with links to all my other threads on it :)
Dec 12 '06 #22

NeoPa
Expert Mod 15k+
P: 31,660
Maybe I should post a thread with links to all my other threads on it :)
Now that's just plain frightening ;).
Dec 12 '06 #23

MMcCarthy
Expert Mod 10K+
P: 14,534
Try this....

Mary

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command56_Click()
  2.  
  3.    Select Case coType
  4.  
  5.    Case "Corporation"
  6.  
  7.       DoCmd.OpenForm "frmTotalRevenue1120", , ,"[ClientAutoID] = " & coClient
  8.  
  9.    End Select
  10.  
  11. End Sub
  12.  
  13.  
Dec 12 '06 #24

P: 99
I hate to say it, and I know ya'll are disappointed, but this thread is closed. Thanks for all the help with this. [Everything is working great] but I know ya'll will miss my constant questions on this subject; so I'll make sure to ask 3x as many questions on the other threads. Thanks guys :)
Dec 13 '06 #25

NeoPa
Expert Mod 15k+
P: 31,660
Thanks for the feedback Robert (Not about more coming :( just that this one's over and done with Phew :wipes brow: ).
Dec 14 '06 #26

Post your reply

Sign in to post your reply or Sign up for a free account.