Display Text Value not ID

scouser

scouser

Registered User.
Local time
Today, 22:41
Joined
Nov 25, 2003
Messages
767
I have a form frmOrders that has 2 combos:
cboMake
cboModel
I want the values selected to be displayed in the invoice report. Currenlty the MakeID and ModelID values are displayed:
i.e. 5 and 7.
I want to display Ford and Mondeo.

The report is based on a query. I selected the MakeID and ModelID (giving 5 and 7). I tried to add to the criteria for that field:
[Forms]![frmOrders]![frmOrderDetails]![cboMakeID]
[Forms]![frmOrders]![frmOrderDetails]![cboModelID]

This did not work!!

Any ideas as to how I can get the report to print meaningful data?

See attached jpeg.
Many Thanks,
Phil.
 

Attachments

  • Invoice Report.JPG
    Invoice Report.JPG
    35.7 KB · Views: 124
  • Invoice Report Query.JPG
    Invoice Report Query.JPG
    68.2 KB · Views: 119
You are selecting the MakeID and ModelID. You need to join to the Make and Model tables in order to get the text values.

You are being confused because you have used the lookup "feature" in your table so you think that since you "see" text that is what is in the query when in fact that is not the case at all. I NEVER use this "feature". It was intended as an aid for novices who didn't know how to write their own queries. If I really want to see the text value rather than the numeric value, I create a query. If these fields are not required, you need to use left joins to the lookup tables rather than inner joins.
 
Thanks

Pat you must be getting sick of me!!

So curretly the values displayed on my Form reference MakeID and ModelID and there control source is qryOrderDetails. The query in turn gets these values from 'tblOrderDetails'.

tblOrderDetails contains the foreign keys MakeID and ModelID, and it is these that allow me to relate order details to vehicle makes and models.

So therefore are you suggesting I remove MakeID and ModelID from qryOrderDetails?

If this is the case then these fields would no longer appear on the frmOrderDetails........cascading combos etc.........

Am I looking too deep? Is it much easier than that?
Once again Pat, many thanks.
Phil.
 
So where am I going wrong?

I created a new query qryMakesModels as suggested by Pat from tables tblMakes and tblModels.
I added this query to qryOrderDetails (not sure what a left join is?). I then added fields Makes and Models to qryOrderdetails.

When run this does return the TEXT value for Vehicle Make and Model as I wanted (as opposed ID Values).

I made frmOrderDetails cboMakeID and ModelID Control Source to equal the text values for Makes and Models.

Now the combos will not work. When selected I get an error "The value you entered isn't valid for this field". I presume this is because the foreign keys in tblOrderDetails are 'Number' and the Make and Model fields are text. I can not see a simple (there's that word again!) resolution to this problem?

Any thoughts?
Thanks in advance,
Phil.
 

Attachments

Forget the Sample DB

Forget the sample DB is is RUBBISH!! Just had another look and it is drastically flawed!

Back to the drawing board!!
:eek:
 
You are getting confused between what you want to "see" and what you want to "store". Since you were using combos, you get to "see" the text field and "store" the id field. Sometimes you may not want to use a combo because the RowSource is too large. In that case you would probably want to "see" both the id and the text although you would still only store the id.

In reports, you don't want to display combos, you only want to display a text field. In this case, you would join the two tables and just select the text field because reports are display only. There won't be any updating.

Change the form back to the way it was when I sent it to you.
 
That was quick!

Pat I have reverted back. I have imported my report + the query it is based on. It displays blank data? I can only presume it is because I have scaled down the DB to fit on the Forum and I have messed something up!

In this case, you would join the two tables and just select the text field because reports are display only. There won't be any updating.
Click to expand...

Sorry Pat I am a little confused here. I understand reports are display only. I have joined the 2 tables to make query qryMakesModels. This includes fields:
MakeID
Make
ModelID
Model

The "Invoice" report is based on a query which is based on multiple tables. This is where I am confused? Do I need to include the new query in this and then add fields Make and Model. If yes, how will this refelct the current order?

I think I am confusing matters!

On a lighter note I see you are writing a book? How is it coming along? Get it published in the UK and you have at least one guranteed sale!!

Many Thanks, for both your time and advice.
Phil.
 

Attachments

The report's RecordSource needs to be a query. If you don't already have one, create it. Add the make and model tables and join them to the foreign key fields in the main table. Select the columns you want from each table. For the report, you don't need the id field, you need the text field from the make and model tables.
 
Thanks

Thanks once again. The reports record source is a query, qryInvoiceQuery.
Right I added the new query 'qryMakesModels' to the existing query 'qryInvoiceQuery'. I joined field "MakeID" from qryMakesModels' to tblOrderDetails "MakeID". I then added fields Make and Model to the query.

This kind of works (see screen jpeg). I can see what is happening but do not know why!

Form OrderDetails is a single form. The user can select one Make and Model from frmOrderDetail for each order (due to the constraints of the cascading combo). What is happening is record of the SAME order is being created for EVERY Make and Model:

i.e. Order 333 = 1 Line. The user selects Make Ford Model Galaxy.
The Make Ford has 3 Models. The order 333 appears 3 times, once for each Model?

The screen dump explians it all!!

I feel WE are getting there.
Thanks Pat.
Phil.
 

Attachments

  • Report.JPG
    Report.JPG
    40 KB · Views: 137
I meant.......

You can not use Continuous Forms (well you can) but when you add a second line the value in the second combo dissappears. It reappears when you re-select that record. Just a quirk of Access (explained much better by Pat).

Rich do you have a solution to my report problem as per my last post?
Cheers,
Phil.
 
Tried

Tried to add where condition to Make Field on qryInvoiveQuery:
Code: 
[Forms]![frmOrders]![cboMakeID]=[Forms]![frmOrders]![cboMakeID]

Didn't work as I am GUESSING!!!!!!!
:confused:
 
The duplication is probably caused because you have not joined the tables correctly. Post the query that the report uses and any subqueries also.
 
Post

Thanks guys. I will have to butcher my DB and hope it works!!
I will post shortly.
Thanks,
Phil.
 
At Last

Hello again!! Converting to 97 was OK as the DB compressed nice and small. However the 2002 vresion proved more difficult so I have had to split it into 2 DB's. I will explain:

Forum Help 2002 Version 1: This contains the Tables + Forms
Forum Help 2002 Version 2: Thsi contains the Queries and Reports

I would advise importing from 2 into 1 as that way the relationships remain (said that for my own piece of mind!!).

I can not thank you enough for your help. I hope you can se that I have put lots of hours into this project (and all for the love of my brother!!).

Many Thanks,
Phil.
 

Attachments

There's no data in your order tables so I can't test this but I think your query should look more like


SELECT tblOrders.CustomerName, tblCustomers.AddressLine1, tblCustomers.AddressLine2, tblCustomers.AddressLine3, tblCustomers.City, tblCustomers.County, tblCustomers.PostalCode, tblOrders.ServiceAddress1, IIf(IsNull([FaxNumber]),"- - - - - - - - - - - ",[FaxNumber]) AS Fax, tblOrders.CustomerID, [FirstName] & " " & [LastName] AS Employees, tblOrders.OrderID, IIf(IsNull([PurchaseOrderNumber]),"NOT ADVISED ",[PurchaseOrderNumber]) AS PO, tblOrders.OrderDate, tblOrders.ConfirmedServiceDate, tblOrderDetails.ServiceID, tblOrderDetails.ServicePrice, tblServices.ServiceDescription, tblOrderDetails.VehicleReg, tblOrderDetails.Quantity, tblOrderDetails.LineTotal, tblOrderDetails.Discount, tblOrders.Vat, tblOrders.TotalGross, tblOrders.Total, qryMakesModels.Make, qryMakesModels.Model
FROM tblCustomers RIGHT JOIN (tblServices RIGHT JOIN ((tblEmployees RIGHT JOIN tblOrders ON tblEmployees.EmployeeID = tblOrders.EmployeeID) RIGHT JOIN (qryMakesModels RIGHT JOIN tblOrderDetails ON qryMakesModels.MakeID = tblOrderDetails.MakeID) ON tblOrders.OrderID = tblOrderDetails.OrderID) ON tblServices.ServiceID = tblOrderDetails.ServiceID) ON tblCustomers.CustomerID = tblOrders.CustomerID
GROUP BY tblOrders.CustomerName, tblCustomers.AddressLine1, tblCustomers.AddressLine2, tblCustomers.AddressLine3, tblCustomers.City, tblCustomers.County, tblCustomers.PostalCode, tblOrders.ServiceAddress1, IIf(IsNull([FaxNumber]),"- - - - - - - - - - - ",[FaxNumber]), tblOrders.CustomerID, [FirstName] & " " & [LastName], tblOrders.OrderID, IIf(IsNull([PurchaseOrderNumber]),"NOT ADVISED ",[PurchaseOrderNumber]), tblOrders.OrderDate, tblOrders.ConfirmedServiceDate, tblOrderDetails.ServiceID, tblOrderDetails.ServicePrice, tblServices.ServiceDescription, tblOrderDetails.VehicleReg, tblOrderDetails.Quantity, tblOrderDetails.LineTotal, tblOrderDetails.Discount, tblOrders.Vat, tblOrders.TotalGross, tblOrders.Total, qryMakesModels.Make, qryMakesModels.Model;

and what's line total in your table?
 
Hi

Rich if you open frmOrders you can simply select a customer name from the combo, the fields auto-populate.

The Same with the sub form. Select Vehicle Make + Vehicle Model from the combos and then the service description. The remaining fields auto-populate.
Line total totals the value for the line. So service = £25 Quantity = 2 Line Total = £50.

I would add data and re-post but the Database doubles in size just by adding 1 record, it is then too big to post!!

Help appreciated,
Phil.
 
Form orders has code that 97 doesn't recognise and therefore won't open, try pasting the sql I posted into a new blank query, open it and see if it contains the correct number of records
 
Added

Hi Rich. I did as you suggested and it acted in the same way i.e. An order Detail was created for each Vehicle Make and Model?

See Re-Posted DB.
Regards,
Phil.
 

Attachments

You must log in or register to reply here.

Similar threads

amorosik
How to display a 'running total' on a report preview AND on pdf/print?
Replies
10
Views
741
Ken Sheridan
Ken Sheridan
B
Enter Parameter Value? I cannot seem to use sum in footer. my field or control not found. Need help please
Replies
18
Views
548
Pat Hartman
P
M
How to show the value of criteria on query to report
Replies
5
Views
663
xavier.batlle
X
E
Report shows calculation of check value wrong
Replies
3
Views
503
Pat Hartman
P
T
Display text in calculated query field based on multiple checkboxes
Replies
4
Views
539
timbits35
T

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom