help
- Thread starter ppoole16
- Start date
ppoole16,
That assumes both criteria fields are on form Test and are
numeric.
Wayne
Code:
DoCmd.OpenReport stDocName, acPreview, , "[ID] = " & forms![Test]![Booking SheetID] & " And [Car Number] = " & forms![Test]![Car Number]That assumes both criteria fields are on form Test and are
numeric.
Wayne
Wayne, I must admit I'm still working on the problem that I had a couple of weeks ago that you helped me with. My lack of experience and my hard head are realling making this impossible. I'm still trying to get a repot that displays the data in the current form and subform. I've gotten it to work with your previous advice, however the user is prompted to input the unique id in the subform before the report will display. I'd like to make it work without the prompt. You've been so helpful and I'd hate to give up after this much struggle. If you have the time and are willing, I'd like to post the database to see if you can help. My only problem is that it's 5.5 MB and I don't have a website to post it to. If you're willing and you have any ideas on how to get it to you, I'd be very appreciative.
Thanks so much.
-Phil
Thanks so much.
-Phil
Phil,
Wow, you can't mail me a 5.5 meg database! Have you
compacted, ZIPped, etc?
The prompt is because the query doesn't recognize one of
the criteria names used. If you have the form open, go to
the database window, run the query and one at a time remove
the criteria. When it doesn't prompt, the last one was the
trouble.
Wayne
Wow, you can't mail me a 5.5 meg database! Have you
compacted, ZIPped, etc?
The prompt is because the query doesn't recognize one of
the criteria names used. If you have the form open, go to
the database window, run the query and one at a time remove
the criteria. When it doesn't prompt, the last one was the
trouble.
Wayne
Assuming ID and Car Number are numeric fields and ID is on the main form while Car Number is on the subform, try this (using the correct names of the forms):-
DoCmd.OpenReport stDocName, acPreview, , "[ID]=" & Forms![nameOfMainForm]![ID] & " AND [Car Number]=" & Forms![nameOfMainForm]![nameOfSubform].Form![Car Number]
If e.g. Car Number is a text field, its value has to be surrounded by single quotes:-
DoCmd.OpenReport stDocName, acPreview, , "[ID]=" & Forms![nameOfMainForm]![ID] & " AND [Car Number]='" & Forms![nameOfMainForm]![nameOfSubform].Form![Car Number] & "'"
The syntax for referencing a control on a subform is:-
Forms![nameOfMainForm]![nameOfSubform].Form![nameOfControl]
If the OpenReport method is run from the main form, you can use the key word Me to replace Forms![nameOfMainForm]
Hope this helps.
Last edited:
G'd morning to all:
Jon K:
I unless you had tested that code, i will conclude that the code works in both ways. I test my code before post it, but i'd tryed (by chance) with two fields one numerical and one string
but in any moment i sourrended any of them with quotes.
Regards
Estuardo
Jon K:
I unless you had tested that code, i will conclude that the code works in both ways. I test my code before post it, but i'd tryed (by chance) with two fields one numerical and one string
but in any moment i sourrended any of them with quotes.
Regards
Estuardo
Ok, I'm sure it's just my inexperience or hard head, but after three weeks I'm still not getting it. I have no doubt your solutions will work...just not for me. I've posted my dbase at the link below. I know it's a mess. Since I don't have the latest version of it here at home, I just made a subform for an example. I tried the code above, and Wayne's trial and error, but it keeps prompting for the unique id of the subform.
Thanks for any help.
http://geocities.com/philpoole16
(sorry it's a geocities site)
Thanks for any help.
http://geocities.com/philpoole16
(sorry it's a geocities site)
Phil,
OK, here's all you do:
Remove the criteria in [Booking Query 1] for the car.
Your tables are joined, so there is no need to specify it. As a
separate problem, we can figure why the form doesn't
display the car info.
The syntax that we (Jon and I) were specifying was if you
were doing:
DoCmd.OpenReport "Booking Report 1" ...
and passing the parameters then. Your way is fine, but you
should consider putting "acPreview" as the second parameter
after the report name. It might save some paper.
Wayne
OK, here's all you do:
Remove the criteria in [Booking Query 1] for the car.
Your tables are joined, so there is no need to specify it. As a
separate problem, we can figure why the form doesn't
display the car info.
The syntax that we (Jon and I) were specifying was if you
were doing:
DoCmd.OpenReport "Booking Report 1" ...
and passing the parameters then. Your way is fine, but you
should consider putting "acPreview" as the second parameter
after the report name. It might save some paper.
Wayne
I think it's close, but now it's prompting for [ID].
http://geocities.com/philpoole16
if you make the changes and want to post it to the site, my geocities user name is philpoole16 and password is 123456.
Thanks again.
http://geocities.com/philpoole16
if you make the changes and want to post it to the site, my geocities user name is philpoole16 and password is 123456.
Thanks again.
Last edited:
ppoole16
I pass the parameters to the query, and remove it from the filter. it seems to be working...
---
I'd try to upload the file but it's said i have no authorization.
any ways.
I just remove the report's filter in the form. Now the code behind your button is:
and because you already had the "Booking Sheet Number" as parameter in the query (you were doing it twice. Once in the form and once in the query), i just add to your query under the [car number] the following:
and that's all
now is working fine
Good Luck
Estuardo
I pass the parameters to the query, and remove it from the filter. it seems to be working...
---
I'd try to upload the file but it's said i have no authorization.
any ways.
I just remove the report's filter in the form. Now the code behind your button is:
Code:
DoCmd.OpenReport stDocName, acPreviewand because you already had the "Booking Sheet Number" as parameter in the query (you were doing it twice. Once in the form and once in the query), i just add to your query under the [car number] the following:
Code:
[Forms]![BookingSheet]![Test].[Form]![Car Number]and that's all
now is working fine
Good Luck
Estuardo
Last edited:
Phil,
I didn't even notice your subform at the top-right (or the button).
Oops.
The problem is that you were looking for "[ID] =...", you need
to look for "[Booking Sheet Number] = ..." as below.
Wayne
I didn't even notice your subform at the top-right (or the button).
Oops.
The problem is that you were looking for "[ID] =...", you need
to look for "[Booking Sheet Number] = ..." as below.
Code:
"[Booking Sheet Number]=" & Forms![BookingSheet]![ID] & " AND [Car Number]=" & Forms![BookingSheet]![Test].Form![Car Number]Wayne
Great! Now that I'm at work I've been trying to get it to work on the real dbase. It worked fine at home on my example but I can't take what you explained and apply it to this one. I need to do the same thing based on the [ChargeID] field for the Charges subform. I know it has something to do with the query critera. Will you please take one more look?
Thanks sooooo much.
http://geocities.com/philpoole16
Thanks sooooo much.
http://geocities.com/philpoole16
Phil,
In your last db, get the query for the report in design view.
Look for the criteria (not from the booking table), but from
the charge table.
In the Column where is says ChargeID, change it to Booking
Sheet Number AND remove the criteria.
The nice way that you have your relationships set up means
that all you needed when you defined a Booking Number on
the form, all you had to do was to drag the other two tables,
cars and charges, into the query. Access made the links and
will retrieve the right records.
Relax, you're working too hard.
Wayne
In your last db, get the query for the report in design view.
Look for the criteria (not from the booking table), but from
the charge table.
In the Column where is says ChargeID, change it to Booking
Sheet Number AND remove the criteria.
The nice way that you have your relationships set up means
that all you needed when you defined a Booking Number on
the form, all you had to do was to drag the other two tables,
cars and charges, into the query. Access made the links and
will retrieve the right records.
Relax, you're working too hard.
Wayne
