Navigating through Forms, Based on controls.

[robertmeyer1]

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: tblClientInform ation and tblCompanyType. The form for tblClientInform ation 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.

[MSeda]

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

[MSeda]

the form opened in case "B" should read "Form B". sorry for the typo

[NeoPa]

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.

[robertmeyer1]

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.

[NeoPa]

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.

[MMcCarthy]

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.

GLOBAL coType As String

Then in the first form pass the value into the global variable in the after update event of the combobox.

coType = comboboxname

Then for the SELECT CASE statement.

 
Select Case coType

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

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

Mary

[robertmeyer1]

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.

[MMcCarthy]

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

[robertmeyer1]

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 "frmTotalRevenu e1120", , , "[ClientAutoID] = CoClient"

Case "B"
DoCmd.OpenForm "frmTotalRevenu e1040", , , "[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 frmTotalReveneu e1040 (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.

[MSeda]

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 "frmTotalRevenu e1120", , , "[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_database_global_variable.htm

[robertmeyer1]

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 "frmTotalRevenu e1120", , , "[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:

Private Sub Command56_Click()
Select Case coType

Case "Corporation"
DoCmd.OpenForm "frmTotalRevenue1120", , , "[ClientAutoID] = Me.ClientAutoID"
WHERE DoCmd.OpenForm "frmTotalRevenue1120", , ,"[ClientAutoID] = " & me.Client

End Select
End Sub

An error message pops up after the second “frmTotalRevenu e1120” and says “expected end of statement”. ClientAutoId is a numeric number.

[NeoPa]

Ok. Still no luck. Here’s what I have now:

Private Sub Command56_Click()
Select Case coType

Case "Corporation"
DoCmd.OpenForm "frmTotalRevenue1120", , , "[ClientAutoID] = Me.ClientAutoID"
WHERE DoCmd.OpenForm "frmTotalRevenue1120", , ,"[ClientAutoID] = " & me.Client

End Select
End Sub

An error message pops up after the second “frmTotalRevenu e1120” 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.

[MSeda]

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 "frmTotalRevenu e1120", , , "[ClientAutoID] = " & Me.ClientAutoID

Case “B”
DoCmd.OpenForm "frmTotalRevenu e1040 , "[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.

[robertmeyer1]

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 ClientCompanyTy pe_AfterUpdate( )
GloOpt = Me.ClientCompan yType
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)