Certain Form load according to combo box value on currnet form

[VWP1]

I have an If-Then VBA procedure that's nested, and works like a charm,
however, I've got around twenty different forms that are "page 2" forms, in
an application. Page one is the form containing the person's name, address,
etc.. in the last textbox of page one is the choice for which position the
applicant is interested in. Now, if all the "page 2" forms were idnetical,
I've got VBA for that situation, but each page 2 form is unique.

I am interested in a VBA procedure that works so that the drop down combo
box (containing the job openings which are listed) is filled, the correct
"page 2" form will load. I'm imagining the use of a table with the list, and
corresponding form names would be there, but I've tried so many things in the
past few months.

Please help.

 

[Maurice]

Ok, what we need to know is what identifies the forms as unique. Is it the
name of the form or...

If i understand correctly a form which resembles the next page should be
opened based on the combo filled with positions available. After chosing a
position what is the criteria for opening the form, is it the name of the
position or is it an identifier for a certain group.

Maybe post a small example so we can think aloud with you.

 

[VWP1]

This is what I am trying to avoid:

Private Sub emplt_pstn__AfterUpdate()

On Error GoTo Err_emplt_pstn__AfterUpdate

Dim stDocName As String
Dim stLinkCriteria As String
If Me![emplt_pstn_] = 1 Then

stDocName = "f_02_custodian"

'stLinkCriteria = "[applcnID]=" & Me![applcnID]
DoCmd.close
DoCmd.OpenForm stDocName, , , stLinkCriteria

Else

If Me![emplt_pstn_] = 2 Then

stDocName = "f_02_admin_clerk"

'stLinkCriteria = "[applcnID]=" & Me![applcnID]
DoCmd.close
DoCmd.OpenForm stDocName, , , stLinkCriteria

Else

If Me![emplt_pstn_] = 3 Then

stDocName = "f_02_secretary"

'stLinkCriteria = "[applcnID]=" & Me![applcnID]
DoCmd.close
DoCmd.OpenForm stDocName, , , stLinkCriteria

Else

If Me![emplt_pstn_] = 4 Then

stDocName = "f_02_eqp_rpr"

'stLinkCriteria = "[applcnID]=" & Me![applcnID]
DoCmd.close
DoCmd.OpenForm stDocName, , , stLinkCriteria


Else

If Me![emplt_pstn_] = 5 Then

stDocName = "f_02_rep"

'stLinkCriteria = "[applcnID]=" & Me![applcnID]
DoCmd.close
DoCmd.OpenForm stDocName, , , stLinkCriteria


Else

If Me![emplt_pstn_] = 6 Then

stDocName = "f_02_rep_spvsr"

'stLinkCriteria = "[applcnID]=" & Me![applcnID]
DoCmd.OpenForm stDocName, , , stLinkCriteria


Else

If Me![emplt_pstn_] = 7 Then

stDocName = "f_02_crew_mbr"

'stLinkCriteria = "[applcnID]=" & Me![applcnID]
DoCmd.OpenForm stDocName, , , stLinkCriteria


Else

If Me![emplt_pstn_] = 8 Then

stDocName = "f_02_frmn"

'stLinkCriteria = "[applcnID]=" & Me![applcnID]
DoCmd.close
DoCmd.OpenForm stDocName, , , stLinkCriteria


Else

If Me!emplt_pstn_] = 9 Then

stDocName = "f_02_inet_liaison"

'stLinkCriteria = "[applcnID]=" & Me![applcnID]
DoCmd.OpenForm stDocName, , , stLinkCriteria


Else

If Me![emplt_pstn_] = 10 Then

stDocName = "f_02_scrty"

'stLinkCriteria = "[applcnID]=" & Me![applcnID]
DoCmd.OpenForm stDocName, , , stLinkCriteria


Else

If Me![emplt_pstn_] = 11 Then

stDocName = "f_02_public"

'stLinkCriteria = "[applcnID]=" & Me![applcnID]
DoCmd.OpenForm stDocName, , , stLinkCriteria


Else

If Me![emplt_pstn_] = 12 Then

stDocName = "f_02_trainee"

'stLinkCriteria = "[applcnID]=" & Me![applcnID]
DoCmd.OpenForm stDocName, , , stLinkCriteria


Else

If Me![emplt_pstn_] = 13 Then

stDocName = "f_02_prsnl"

'stLinkCriteria = "[applcnID]=" & Me![applcnID]
DoCmd.OpenForm stDocName, , , stLinkCriteria


Else

If Me![emplt_pstn_] = 14 Then

stDocName = "f_02_mgr"

'stLinkCriteria = "[applcnID]=" & Me![applcnID]
DoCmd.OpenForm stDocName, , , stLinkCriteria


Else

If Me![emplt_pstn_] = 15 Then

stDocName = "f_02_hr"

'stLinkCriteria = "[applcnID]=" & Me![applcnID]
DoCmd.close
DoCmd.OpenForm stDocName, , , stLinkCriteria


Else

If Me![emplt_pstn_] = 16 Then

stDocName = "f_02_cstmr_svc"

'stLinkCriteria = "[applcnID]=" & Me![applcnID]
DoCmd.close
DoCmd.OpenForm stDocName, , , stLinkCriteria


Else

If Me!emplt_pstn_] = 17 Then

stDocName = "f_02_sales_rep"

'stLinkCriteria = "[applcnID]=" & Me![applcnID]
DoCmd.close
DoCmd.OpenForm stDocName, , , stLinkCriteria


Else

If Me![emplt_pstn] = 18 Then

stDocName = "f_02_off_spvsr"

'stLinkCriteria = "[applcnID]=" & Me![applcnID]
DoCmd.close
DoCmd.OpenForm stDocName, , , stLinkCriteria

End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If

Exit_emplt_pstn__AfterUpdate:
Exit Sub

Err_emplt_pstn__AfterUpdate:
MsgBox Err.Description
Resume Exit_emplt_pstn__AfterUpdate

End Sub
_____________________________________
Now, instead of this laborious VBA above, which is cumbersome when I change,
add, or delete a position, I hope to have the next form (f_02...) be the
appropriate form for the position; a custodian doesn't need to indicate
his/her typing speed, and a sales representative doesn't need to know
custodial terminology, such as a floor buffer, or other equipment. The 2nd
form is determined by the applicant's choice in their job interest.

=============================

 

[Maurice]

I see what you mean

I would go for a so called 'codetable' which consists of two fields where
the first field resembles the value of the combobox and the second the
formname. Fill the table with the various options like:

your fields could be called; lkValue (lookupvalue) and ObjectName

1 - "f_02_custodian"

etc...

After that you write the following in the after update of the combo:

dim strFormName as string

strFormName=dlookup("ObjectName", "tablename","lkValue=" & me.[emplt_pstn_])

docmd.openform strformname

That way you can rename the formnames whenever you like because you don't
have to change any code to keep the functionality working.

I often use this technique. I also place reports in there and with a select
statement you can differ between docmd.openform and docmd.openreport.

If the lookupvalue is a string type you have to adjust the dlookup where
statement with the apropriate apostrophes like. where...= '" &
me.Me![emplt_pstn_] & "'"

So adjust the tablename in the dlookup and give it a try.

hth
--
Maurice Ausum

This is what I am trying to avoid:

Private Sub emplt_pstn__AfterUpdate()

On Error GoTo Err_emplt_pstn__AfterUpdate

Dim stDocName As String
Dim stLinkCriteria As String
If Me![emplt_pstn_] = 1 Then

stDocName = "f_02_custodian"

'stLinkCriteria = "[applcnID]=" & Me![applcnID]
DoCmd.close
DoCmd.OpenForm stDocName, , , stLinkCriteria

Else

If Me![emplt_pstn_] = 2 Then

stDocName = "f_02_admin_clerk"

'stLinkCriteria = "[applcnID]=" & Me![applcnID]
DoCmd.close
DoCmd.OpenForm stDocName, , , stLinkCriteria

Else

If Me![emplt_pstn_] = 3 Then

stDocName = "f_02_secretary"

'stLinkCriteria = "[applcnID]=" & Me![applcnID]
DoCmd.close
DoCmd.OpenForm stDocName, , , stLinkCriteria

Else

If Me![emplt_pstn_] = 4 Then

stDocName = "f_02_eqp_rpr"

'stLinkCriteria = "[applcnID]=" & Me![applcnID]
DoCmd.close
DoCmd.OpenForm stDocName, , , stLinkCriteria


Else

If Me![emplt_pstn_] = 5 Then

stDocName = "f_02_rep"

'stLinkCriteria = "[applcnID]=" & Me![applcnID]
DoCmd.close
DoCmd.OpenForm stDocName, , , stLinkCriteria


Else

If Me![emplt_pstn_] = 6 Then

stDocName = "f_02_rep_spvsr"

'stLinkCriteria = "[applcnID]=" & Me![applcnID]
DoCmd.OpenForm stDocName, , , stLinkCriteria


Else

If Me![emplt_pstn_] = 7 Then

stDocName = "f_02_crew_mbr"

'stLinkCriteria = "[applcnID]=" & Me![applcnID]
DoCmd.OpenForm stDocName, , , stLinkCriteria


Else

If Me![emplt_pstn_] = 8 Then

stDocName = "f_02_frmn"

'stLinkCriteria = "[applcnID]=" & Me![applcnID]
DoCmd.close
DoCmd.OpenForm stDocName, , , stLinkCriteria


Else

If Me!emplt_pstn_] = 9 Then

stDocName = "f_02_inet_liaison"

'stLinkCriteria = "[applcnID]=" & Me![applcnID]
DoCmd.OpenForm stDocName, , , stLinkCriteria


Else

If Me![emplt_pstn_] = 10 Then

stDocName = "f_02_scrty"

'stLinkCriteria = "[applcnID]=" & Me![applcnID]
DoCmd.OpenForm stDocName, , , stLinkCriteria


Else

If Me![emplt_pstn_] = 11 Then

stDocName = "f_02_public"

'stLinkCriteria = "[applcnID]=" & Me![applcnID]
DoCmd.OpenForm stDocName, , , stLinkCriteria


Else

If Me![emplt_pstn_] = 12 Then

stDocName = "f_02_trainee"

'stLinkCriteria = "[applcnID]=" & Me![applcnID]
DoCmd.OpenForm stDocName, , , stLinkCriteria


Else

If Me![emplt_pstn_] = 13 Then

stDocName = "f_02_prsnl"

'stLinkCriteria = "[applcnID]=" & Me![applcnID]
DoCmd.OpenForm stDocName, , , stLinkCriteria


Else

If Me![emplt_pstn_] = 14 Then

stDocName = "f_02_mgr"

'stLinkCriteria = "[applcnID]=" & Me![applcnID]
DoCmd.OpenForm stDocName, , , stLinkCriteria


Else

If Me![emplt_pstn_] = 15 Then

stDocName = "f_02_hr"

'stLinkCriteria = "[applcnID]=" & Me![applcnID]
DoCmd.close
DoCmd.OpenForm stDocName, , , stLinkCriteria


Else

If Me![emplt_pstn_] = 16 Then

stDocName = "f_02_cstmr_svc"

'stLinkCriteria = "[applcnID]=" & Me![applcnID]
DoCmd.close
DoCmd.OpenForm stDocName, , , stLinkCriteria


Else

If Me!emplt_pstn_] = 17 Then

stDocName = "f_02_sales_rep"

'stLinkCriteria = "[applcnID]=" & Me![applcnID]
DoCmd.close
DoCmd.OpenForm stDocName, , , stLinkCriteria


Else

If Me![emplt_pstn] = 18 Then

stDocName = "f_02_off_spvsr"

'stLinkCriteria = "[applcnID]=" & Me![applcnID]
DoCmd.close
DoCmd.OpenForm stDocName, , , stLinkCriteria

End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If

Exit_emplt_pstn__AfterUpdate:
Exit Sub

Err_emplt_pstn__AfterUpdate:
MsgBox Err.Description
Resume Exit_emplt_pstn__AfterUpdate

End Sub
_____________________________________
Now, instead of this laborious VBA above, which is cumbersome when I change,
add, or delete a position, I hope to have the next form (f_02...) be the
appropriate form for the position; a custodian doesn't need to indicate
his/her typing speed, and a sales representative doesn't need to know
custodial terminology, such as a floor buffer, or other equipment. The 2nd
form is determined by the applicant's choice in their job interest.

=============================

Ok, what we need to know is what identifies the forms as unique. Is it the
name of the form or...

If i understand correctly a form which resembles the next page should be
opened based on the combo filled with positions available. After chosing a
position what is the criteria for opening the form, is it the name of the
position or is it an identifier for a certain group.

Maybe post a small example so we can think aloud with you.

 

[VWP1]

May I send you the sample database? I have a great deal to explain, otherwise.

 

[Maurice]

Hmm, try asking little pieces.

This NG has a lot of viewers. If i don't know a solution someone else might
and will jump into this discussion as well.

I tend not to accept anything via mail (for obvious reasons). Not that i
don't want to help don't get me wrong...

so let's hear it ;-)

 

[VWP1]

'copied directly from Maurice:
'Dim strFormName As String
'strFormName = DLookup("ObjectName", "tablename", "lkValue=" &
Me.[emplt_pstn_])

'DoCmd.OpenForm strFormName

'assuming that the name of the combo box has been changed from emplt_pstn_ to
'Go_to_Pstn02
'meaning "page 2 of the current application, for that position"
_______________________________________________

Private Sub Go_to_Pstn02_AfterUpdate()
On Error GoTo Err_Go_to_Pstn02_AfterUpdate

Dim strFormName As String
Dim stLinkCriteria As String

strFormName = DLookup("ObjectName", "codetable", "lkValue=" &
Me.[Go_to_Pstn02])

DoCmd.OpenForm strFormName

Exit_Go_to_Pstn02_AfterUpdate:
Exit Sub

Err_Go_to_Pstn02_AfterUpdate:
MsgBox Err.Description
Resume Exit_Go_to_Pstn02_AfterUpdate

End Sub
______________________________________________
Details in Structure you requested instead of sending you the database:
Table (used for Maurice's suggested text field data):
tablename: codetable
Fields: lkValue (text), ObjectName (text)
Hard Data:
ObjectName lkValue
FormApplicationPg02Cstdn Custodian
FormApplicationPg02CstmrSvc Customer Service
FormApplicationPg02InetLiaison Internet Iaison
FormApplicationPg02AdmnAsst Aministrative Assistant
FormApplicationPg02SalesRep Sales Representative

Table (used for numeric field data in the future only):
tablename: PositionRef
Fields: PositionID (Autonumber), Position (text), PositionForm_ (Long
Integer)
Hard Data:
ID Position PositionForm_
1 Custodian FormApplicationPg02Cstdn
2 Administrative Assistant FormApplicationPg02AdmnAsst
3 Sales Representative FormApplicationPg02SalesRep
4 Internet Liaison FormApplicationPg02InetLiaison
5 Customer Service FormApplicationPg02CstmrSvc

Table (used for numeric field data in the future only):
tablename: FormNameRef
Fields: FormNameID (Autonumber), FormName (text)
Hard data:
ID FormName
1 FormApplicationPg01
2 FormApplicationPg02CstmrSvc (Customer Service)
3 FormApplicationPg02Cstdn (Custodian)
4 FormApplicationPg02AdmnAsst (Administrative Assistant)
5 FormApplicationPg02InetLiaison (Internet Liaison)
of course, there are many other positions....and
subsequent forms ie. FormApplicationPg03 (used for all positions)
Form:
formname: FormApplication
(this is the first page of the application, in data entry mode)
vba: above
record source: codetable

Controls:
textboxes:
SmplApplNmLast (Text)
SmplApplNmFirst (Text)
SSN1 (Integer)
SSN2 (Byte)
SSN3 (Integer)
DOB (Date/Time)
other pertinent info in other textboxes
combobox:
tab order=last on this form
name: Go_to_Pstn02
SELECT PositionRef.[Position]
FROM PositionRef
ORDER BY PositionRef.[Position];
Events:
AfterUpdate: (see vba above)

Form:
formname: FormApplicationPg02Cstdn
controls:
textbox: CustodialQuestion01
textbox: CustodialQuestion02
etc.
Form:
formname: FormApplicationPg02AdmnAsst
corresponding/related AdmnAsst questions...
Form:
formname: FormApplicationPg02SalesRep
corresponding/related SalesRep questions...
Form:
formname: FormApplicationPg02InetLiaison
corresponding/related questions...
Form:
formname: FormApplicationPg02CstmrSvc
corresponding/related questions...


Maurice, I would like to use a number (hidden) for the combo box, instead of
text and go to the appropriate page 2 form, whose formID = whatever. Could
you provide me with the corrections in my VBA for your text to text event,
and then controlchoiceID to formID number version?

 

[VWP1]

Anyone may help me me, ...please.

'copied directly from Maurice:
'Dim strFormName As String
'strFormName = DLookup("ObjectName", "tablename", "lkValue=" &
Me.[emplt_pstn_])

'DoCmd.OpenForm strFormName

'assuming that the name of the combo box has been changed from emplt_pstn_ to
'Go_to_Pstn02
'meaning "page 2 of the current application, for that position"
_______________________________________________

Private Sub Go_to_Pstn02_AfterUpdate()
On Error GoTo Err_Go_to_Pstn02_AfterUpdate

Dim strFormName As String
Dim stLinkCriteria As String

strFormName = DLookup("ObjectName", "codetable", "lkValue=" &
Me.[Go_to_Pstn02])

DoCmd.OpenForm strFormName

Exit_Go_to_Pstn02_AfterUpdate:
Exit Sub

Err_Go_to_Pstn02_AfterUpdate:
MsgBox Err.Description
Resume Exit_Go_to_Pstn02_AfterUpdate

End Sub
______________________________________________
Details in Structure you requested instead of sending you the database:
Table (used for Maurice's suggested text field data):
tablename: codetable
Fields: lkValue (text), ObjectName (text)
Hard Data:
ObjectName lkValue
FormApplicationPg02Cstdn Custodian
FormApplicationPg02CstmrSvc Customer Service
FormApplicationPg02InetLiaison Internet Iaison
FormApplicationPg02AdmnAsst Aministrative Assistant
FormApplicationPg02SalesRep Sales Representative

Table (used for numeric field data in the future only):
tablename: PositionRef
Fields: PositionID (Autonumber), Position (text), PositionForm_ (Long
Integer)
Hard Data:
ID Position PositionForm_
1 Custodian FormApplicationPg02Cstdn
2 Administrative Assistant FormApplicationPg02AdmnAsst
3 Sales Representative FormApplicationPg02SalesRep
4 Internet Liaison FormApplicationPg02InetLiaison
5 Customer Service FormApplicationPg02CstmrSvc

Table (used for numeric field data in the future only):
tablename: FormNameRef
Fields: FormNameID (Autonumber), FormName (text)
Hard data:
ID FormName
1 FormApplicationPg01
2 FormApplicationPg02CstmrSvc (Customer Service)
3 FormApplicationPg02Cstdn (Custodian)
4 FormApplicationPg02AdmnAsst (Administrative Assistant)
5 FormApplicationPg02InetLiaison (Internet Liaison)
of course, there are many other positions....and
subsequent forms ie. FormApplicationPg03 (used for all positions)
Form:
formname: FormApplication
(this is the first page of the application, in data entry mode)
vba: above
record source: codetable

Controls:
textboxes:
SmplApplNmLast (Text)
SmplApplNmFirst (Text)
SSN1 (Integer)
SSN2 (Byte)
SSN3 (Integer)
DOB (Date/Time)
other pertinent info in other textboxes
combobox:
tab order=last on this form
name: Go_to_Pstn02
SELECT PositionRef.[Position]
FROM PositionRef
ORDER BY PositionRef.[Position];
Events:
AfterUpdate: (see vba above)

Form:
formname: FormApplicationPg02Cstdn
controls:
textbox: CustodialQuestion01
textbox: CustodialQuestion02
etc.
Form:
formname: FormApplicationPg02AdmnAsst
corresponding/related AdmnAsst questions...
Form:
formname: FormApplicationPg02SalesRep
corresponding/related SalesRep questions...
Form:
formname: FormApplicationPg02InetLiaison
corresponding/related questions...
Form:
formname: FormApplicationPg02CstmrSvc
corresponding/related questions...


Maurice, I would like to use a number (hidden) for the combo box, instead of
text and go to the appropriate page 2 form, whose formID = whatever. Could
you provide me with the corrections in my VBA for your text to text event,
and then controlchoiceID to formID number version?