Report returns numbers

[Marinus]

Hi Guys,
This time I (a very fresh newbie) really got my knickers in a twist.
When running a report with data from a form;

Private Sub Print_Docket_Click()
Dim strDocName As String
Dim strWhere As String
strDocName = "Docket"
strWhere = "[ID]=" & Me!ID
DoCmd.OpenReport strDocName, acPreview, , strWhere
End Sub

My report named Docket opens and in Field9 and Field10 I get numbers returned instead of the contents of the FormField. The problem is probably that I have selected from a listbox with data in another table.

From here on I sail really blind and would appreciate it if any of you Masters could get my knickers out of the twist.. and help me with a cure for this problem.

Thanks,

Reece

Windows 7
Access 2007

 

[DCrake]

Without looking at you attachment but reading fom your post it sounds like you have table level lookups, get rid of them and use relationships between the two tables.

 

[DCrake]

I just had to have a look to test my theory, glad I did because I need to give you some advice, hich you really need to tak on board. DO NOT name your fields as you have. Field1, Field2, Field3, Etc. How the hell are you going to remember what field is what, let alone anyone else.

Re your other problem, don't know where to start looking as the names are very very hard to follow. CHANGE NOW BEFORE YOU GO ANY FURTHER.

 

[Marinus]

Without looking at you attachment but reading fom your post it sounds like you have table level lookups, get rid of them and use relationships between the two tables.

Thanks for your quick answer, I have made relationships between the tables would that be what you mean?? Sorry for sounding dumb .. Didn't do this for 20 years.. and it ain't like cycling..

 

[Marinus]

I just had to have a look to test my theory, glad I did because I need to give you some advice, hich you really need to tak on board. DO NOT name your fields as you have. Field1, Field2, Field3, Etc. How the hell are you going to remember what field is what, let alone anyone else.

Re your other problem, don't know where to start looking as the names are very very hard to follow. CHANGE NOW BEFORE YOU GO ANY FURTHER.

You are absolutely right, I didn't dare change the names as I am afraid I have to start all over again and I need to show something tomorrow, having done that I will certainly take your advise on board..

 

[Marinus]

I just had to have a look to test my theory, glad I did because I need to give you some advice, hich you really need to tak on board. DO NOT name your fields as you have. Field1, Field2, Field3, Etc. How the hell are you going to remember what field is what, let alone anyone else.

Re your other problem, don't know where to start looking as the names are very very hard to follow. CHANGE NOW BEFORE YOU GO ANY FURTHER.

Followed your advice, makes it easier indeed, this is the clean version, but still get the numbers. Help greatly appriciated..

 

[vbaInet]

You didn't mention what the names of the problem controls are?

 

[boblarson]

I can't find any Field9 or Field10 but I do see that SalesRep returns the number and that is because you have the lookup defined at table level as David had mentioned in Post #2.

You have the combo set to VALUE LIST instead of TABLE/QUERY and you need to remove the lookup at table level. Also, you don't use a combo box on a report. Just add the table Sales_Rep to the query underlying the report and join the SalesRep from the main table to the ID field in the Sales_Rep table. And then use a TEXT BOX on the report to show the Sales_Rep name on the report. The same goes for anything else that is showing like that.

 

[boblarson]

Okay, there's enough wrong with your tables and relationships that I'm doing an overhaul on it and will upload the revised version. You are storing the material ID as text in Docket but it should be storing the ID as Long Integer. The same with Haulier and you don't need the trailer number if it is in the Haulier table. If you need to have multiple trailer numbers for each Haulier then you can store the trailer number in the Docket table (but with that removed from the Haulier table) or you can use a junction table.

So, I'll post back when I'm done.

 

[boblarson]

Okay, here you go.

 

[Marinus]

Okay, here you go.

Great Bob, Thanks so much, I really appreciate the things I here learn so much, when I have this all working I will compile the mistakes (and new things I have learned) and make them available on the board for others to be of help as well.