What is wrong with my form/query?

[PTRACER]

I've uploaded a blank copy of my database, with a few dummy records put in to play with.

The main problem is getting the Manufacturer combo box to display properly on the Repair form. I have tried all kinds of things and I just cannot get it working, I think there is something fundamentally wrong with the design of my database but after hours of Googling, I am at a loss as to what I have done wrong.

If I create a New Repair (via the Customers form), the contents of the Manufacturer combo box don't display unless I refresh the form. However, if I go to Completed Jobs on the switchboard and navigate between two different customers, the box displays perfectly.

On the Repair form, I have Me.Refresh in the "Forms_OnCurrent" event. If I remove this, then the box will display the correct contents on a new repair, but not if I navigate different existing records.

Please help!

 

[georgedwilkinson]

Well, there are certainly some oddities in your design.

First off, a repair is a repair...if you need to enter a new repair, you shouldn't have to concern yourself with the customer and computer (yet). You are using a (probably) non-updateable query to base the repair off of. Why not just base it off a repair (don't concern yourself with the customer/computer.

From a design perspective, wouldn't a repair have both a customer and a computer? I know you may want to track what computer which customer owns, but you shouldn't do that at the repair level. So, your repair "header" should have a FK from your customer and from your computer (unless there are multiple computers per repair). Then it is a matter of a simple lookup for the 2 combo-boxes in question, and you can populate fields for any number of things about customers and/or computers, including the manufacturer of the computer.

 

[PTRACER]

Sorry, you've lost me...The way the database is supposed to work is that one customer owns many computers, and one computer will have many repairs. One computer will have one owner, one repair will have one customer and one computer.

Is that not the same as what you refer to in your second paragraph?

One thing to bear in mind is that I already have a fully populated database with hundreds of customers, hundreds of repairs, etc. etc. so I wouldn't be able to redesign the whole thing from scratch. I was hoping I could get away with doing the Repair form a little differently - maybe there's just something wrong with my queries, I don't know.

 

[georgedwilkinson]

Good luck.

 

[PTRACER]

I solved the problem myself through trying things at random. In two years, this has never worked properly and now it does!

I added DoCmd.RunCommand acCmdRefresh to the end of the New Repair button. So on a new record, I guess it refreshes like I needed it to and with the Me.Refresh "On Current" it works when navigating between records as well.

Hallelujah!