How make a button open a different form based on item on form

[CFP - Jason]

i wanna open a different work order form based on the service selected. but the value in the combo box is not what i need to choose is another column in that table it looks up from. how can can i change the open form command to plugin the name thats in the unshown colnm of a specific field?
thanks
jason

 

[Rich]

& Me.ComboName.Column(1) where column 1 is the second column in your combo

 

[CFP - Russell]

Thanks Rich,

I work with Jason and I am having problems implementing that code. Attatched below is the code for the button.

Private Sub Command128_Click()
On Error GoTo Err_Command128_Click

Dim stDocName As String
Dim stDocName2 As String
Dim stLinkCriteria As String
Dim stLinkCriteria2 As String

DoCmd.SetWarnings False
stDocName = "CreateWO"
stLinkCriteria = "[JobInfoID]=" & [JobInfoID]
DoCmd.OpenQuery stDocName
DoCmd.SetWarnings True

stDocName2 = Me![ServiceName]!Column(6)
stLinkCriteria2 = "[JobInfoID]=" & [JobInfoID]
DoCmd.OpenForm stDocName2


Exit_Command128_Click:
Exit Sub

Err_Command128_Click:
MsgBox Err.Description
Resume Exit_Command128_Click

End Sub

When I click the button to run the code I get the following error, "Property let procedcure not defined and property get procedure did not return an object."

Any help anyone could give me would be greatly appreciated.

Thanks,
Russell

 

[CFP - Russell]

Rich, do you know how to implement that code into my example?

Thanks,
Russell

 

[ghudson]

How many post do you have on this subject?
using combo box to select form to open

Do not use the ! [bang] with Me, use periods.

stDocName2 = Me![ServiceName]!Column(6)
s/b
stDocName2 = ServiceName.Column(6) '= column # 7

Also, I do not see your "where" condition in the OpenForm command.

DoCmd.OpenForm stDocName2
s/b
DoCmd.OpenForm stDocName2,,,stLinkCriteria2

HTH

 

[CFP - Russell]

I changed the code to look like this:

Private Sub Command128_Click()
On Error GoTo Err_Command128_Click

Dim stDocName As String
Dim stDocName2 As String
Dim stLinkCriteria As String
Dim stLinkCriteria2 As String

DoCmd.SetWarnings False
stDocName = "CreateWO"
stLinkCriteria = "[JobInfoID]=" & [JobInfoID]
DoCmd.OpenQuery stDocName
DoCmd.SetWarnings True

stDocName2 = ServiceName.Column(4)
stLinkCriteria2 = "[JobInfoID]=" & [JobInfoID]
DoCmd.OpenForm stDocName2, , , stLinkCriteria2


Exit_Command128_Click:
Exit Sub

Err_Command128_Click:
MsgBox Err.Description
Resume Exit_Command128_Click

End Sub

----------------------------

I get an error that says,

"Compile error:
Method or data member not found"

and access highlights .column

This is the error that I keep getting from this code.

Do you think it is because I am using access 2002, or because the combo box is in a tabbed form and I need to specify the tab that it is on? Also where do I put the where condition?

Thanks,
Russell

 

[ghudson]

Is the name of the combo box "ServiceName"? I am guessing not since you are getting the error for the .column property.

Your where condition looks ok since you copied my command example into your routine. Assuming that your object names are valid.
DoCmd.OpenForm stDocName2, , , stLinkCriteria2

Have you compiled your code?

Also, you should standardize your naming conventions.

prefix text boxes with txt = txtFirstName
prefix combo boxes with cbo = cboServiceName
etc...

HTH

 

[CFP - Russell]

Service name is the name of the combo box.

 

[ghudson]

Since there is a space within your combo box name [you should avoid using spaces when naming your objects] you need to surround it with brackets.

stDocName2 = [Service Name].Column(4)

Is your combo box correctly formatted for the number of columns? Column Count = ?

Column(4) will use the value in the fifth column in your combo box.

HTH

 

[CFP - Jason]

ok heres a senario i have been looking at. having a CB and using that to open a from that creates the record. Then having the form name stored in its table. we have all these records viewed in a list box. now is it feasible to be able to click on the row of the list box and have it open that data in the form that to name is stored in the table? I am sure that the creating the record with the specific form is do-able. but i am clueless on how to make it open the right form from the list box based on the one it was created in. I hope that made sense.
Jason

 

[WayneRyan]

JASON,

DoCmd.OpenForm stDocName, , , stLinkCriteria

Expand the listbox to have an extra column for the form name.

stDocName = Me.YourListBox.Column(1) ... (They start at 0)
stLinkCriteria = "[SomeKeyField] = '" & Me.YourListBox.Column(0) & "'"

This assumes that your key field is character, not numeric.

Wayne

 

[CFP - Jason]

quick question on that one, the column in the first line is 1 the second is 0, is there a reason? and what exactly do u mean "[SomeKeyField] ? is that were is stores the value? if not how do i store it in a table/query other then the one im currently using. i need it to store the form name in the table that the form that is opening uses to create a new record.
Thanks
Jason

oh btw i have 2 columns one is the shown name and the other is the form name, no PK. is that ok?

 

[WayneRyan]

Jason,

I'm just assuming that your listbox has two columns in it:
and is populated something like"

Select YourKeyField, FormName
From   YourTable
Order By YourKeyField;

Where YourKeyField is something like a person's name or ID
number (primary key) and FormName is the name of the form
used to create the record.

The .Column(0) references the name
The .Column(1) references the form name

btw,

Perfect! but most tables have a primary (unique) key.

Wayne

 

[CFP - Jason]

ok great. it opens the correct for each as you instructed. but the value i put in for the "some key field " prompts me to input something. i know for sure now that i have no clue what that is for. also i was still unclear on one thing.
when i open this new form, it makes a record in job information and the opened form uses that table not the one that the form before it uses. now the CB is in the first form but when i open the new one based on the CB i want that form name (col 1) to be stored in a field on the second table but i am unsure how to take the selection and save it in the table. oh and btw the forms opened by the CB are all data entry forms if that helps. i want this stored because i want to have a list box the queries a summary of the reccord and also grabs the formname. so when the form is saved i can use the methd you showed me to reopen the right form later.

thanks so much
Jason

 

[CFP - Jason]

oh regarding the first question

Private Sub Jobtype_AfterUpdate()
stDocName = Me.Jobtype.Column(1)
stLinkCriteria = "[WOType] = '" & Me.Jobtype.Column(0) & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub

WOType is where i want to store the formname in the other table.

Jason

 

[WayneRyan]

Jason,

I don't know how you are inserting into the other
table. You know that the formname is in Me.JobType.Column(1).

I think what you want is on the form that you just opened to
have the default value for [WOType] be:

=Forms![TheFormWithJobTypeCombo]![Jobtype].Column(1)

Wayne