Ok, I can't get it to work, however, my VP has changed her mind how she wants
it to look all together now.
So, now what I have is a form that the user will choose the "Plan
Name"(combo box), and once chosen, I need the next choice "Product
Mnemonic"(combo box) to have the next choice which will be only the "Product
Mnemonics" specific to the "Plan Name" chosen.
Once those choices are made, the next user choice is the "Location"(combo
box) which also is specific to the "Plan Name" and "Product Mnemonic".
Then there is two last choices the user must make before going to the form
and that is the "Effective Date" and "Term Date", also specific to the "Plan
Name", "Product Mnemonic", and "Location".
After all the choices are made, the user will click on the command button
"Click for Contract Info", and the after clicking the user should be taken to
the form that matches all the criteria the user has chosen.
So, Allen, may I ask of you to help get this all coded?
My VP is asking our IT department to send someone to help me code this, but
I woudl really love to do this on my own with your expertise.
Believe it or not, I'm starting in March taking programming classes & year
from January I'll have my programming certificate but for now, I'm just
trying to teach myself Access/VB programming.
Thank you & have a wonderful weekend.
Allen Browne said:
You still have numbers in the where string, and I think you indicated that
it does not work because they need to be names.
The solution will depend on how your tables are set up. One option would be
to change your tables so they store the number and no the name. The OpenForm
would then work. This is probably the best choice, but would require you to
change any other queries, forms, and reports as well.
Another option would be to set up the combos so they have the name in their
2nd column. (They may already have that if they are showing the name.) You
could then code:
stLinkCriteria = "([PlanName] = """ & Me.cmbPlan.Column(1) & _
""") AND ([ProductMnemonic] = """ & Me.cmbProduct.Column(1) & """"
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
Mary A Perez said:
You are so patient.
Here goes:
This is what I get when I choose as the user would the plan and product
names and then click on the command button to get to the form requested:
[PlanName]= 2 and [ProductMnemonic]= 3
Here is the code I have:
stDocName = "frmCurrentRates-SpecificationsMain-South"
stLinkCriteria = "[PlanName]= " & Me.cmbPlan & " and [ProductMnemonic]=
" & Me.cmbProduct & ""
Debug.Print stLinkCriteria
DoCmd.OpenForm stDocName, , , stLinkCriteria
Any ideas?
Allen Browne said:
Add the line:
Debug.Print stLinkCriteria
back in again. What does it generate?
The "syntax error" is normal for numeric fields. To avoid it you need
extra
code to test for nulls, but concentrate on getting this working first,
i.e.
for now supply both values.
The "OpenForm cancelled" message could be due to a badly formed
arguments.
The Debug.Print above will tell us.
Oh, I spoke too soon. What I thought worked wasn't at all. I mis-lead
myself.
Anyhow. Now I get a dialog box when trying to click on my command
button
to
go to the form that says: "The OpenForm actions was cancelled".
It's almost working, because if I don't choose the Plan Name or Product
Mnemonic (I changed it from name to mnemonic) I get an error that says:
"Syntax error (missing operator) in query expression '([PlanName]= and
[ProductMnemonic]=)'."
So, what should I do?
My tables for the forms have ProductMnemonic as it's primary, & I
haven't
changed it since I added the ProductId to the tables/forms. Also, I
have
not
created relationsips with these tables other than the plan and product
tables.
I'm not sure if you needed to know that, but I thought it wouldn't
hurt.
:
Yes. If you have combos on these fields, I suspect they do store
numeric
values.
If so, there are 2 ways to solve the problem. The best would be to
redesign
the table [tblCurrentRates-SpecificationsMain-South] so that it
contains
the
number also, and things can't go wrong.
The easier alternative is to get the text value from the next column
of
the
combo. I can't tell exactly without seeing your data structure and the
RowSource of the combo, but it would be something like this:
stLinkCriteria = "([PlanName]= """ & Me.cmbPlan.Column(1) & _
""") AND [ProductName]= """ & Me.cmbProduct.Column(1) & """)"
Can it be that the Plan Name and Product Name in their individual
tables
have
primary numerical id's and are directly linked (related)?
Should I include these primary id numbers in the form I'm trying to
open?
I hope that doesn't sound too confusing.
Thanks again for all your help.
:
I suspect you have a lookup field that is actually a Number type in
your
table, but uses a combo with a hidden column so that it appears to
be
storing text.
Alternatively, the RowSource of the combo boxes you are using on
your
form
is not returning the name you expect, probably again because the
bound
column is zero-width.
You've made progress in identifying which part is going wrong, so
now
it's
tracking down whether the field is lying to you about what's being
stored
in
the table, or the combo is set up wrongly on the form.
message
Well, Allen.
I did what you said. It is text so I kept the extra quotes.
Then I did the Debug.Print & received the following:
[PlanName]= '1' and [ProductName]= '1'
The SQL view:
WHERE
((([tblCurrentRates-SpecificationsMain-South].PlanName)="Aetna")
AND
(([tblCurrentRates-SpecificationsMain-South].ProductName)="AETUS
HMO"));
So, it looks ok to me, but I'm not sure I know what I'm doing.
So
what
do
you think?
Thanks for your time.
:
If you open your table in design view, what data type are
PlanName
and
ProductName?
If they are Number type fields (not Text type fields), drop the
extra
quotes, i.e.:
stLinkCriteria = "([PlanName]= " & Me.cmbPlan & ") AND
[ProductName]=
" &
Me.cmbProduct & ")"
The brackets are optional. If it still doesn't work, add:
Debug.Print stLinkCriteria
on the next line. Then when it fails to work correctly, open the
Immediate
Window (Ctrl+G) and see what it printed there. Compare it it
what
you
get
in
the WHERE clause if you mock up a query and switch it to SQL
View
(View
menu, from query design view.)
message
Below is a code that will open the form upon clicking the
command
button
for
that form. The form that opens is to be based upon the Plan
and
Product
names chosen from the drop down combo boxes prior to clicking
on
the
form
command button.
The form opens, but does not open to the chosen Plan and
Product
name.
What can I do to fix this?
Thanks in advance for your help.
Private Sub cmdCurrentContractRatesandSpecifications_Click()
On Error GoTo
Err_cmdCurrentContractRatesandSpecifications_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmCurrentRates-SpecificationsMain-South"
stLinkCriteria = "[PlanName]= '" & Me.cmbPlan & "' and
[ProductName]=
'"
& Me.cmbProduct & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_cmdCurrentContractRatesandSpecifica:
Exit Sub
Err_cmdCurrentContractRatesandSpecifications_Click:
MsgBox Err.Description
Resume Exit_cmdCurrentContractRatesandSpecifica
End Sub
