Hi, I'm pretty new to access. I have an assigment whereby I have to create a database system for a video shop. I'm cracking along but I've stumbled.
I have 5 tables, tblCustomer, tblVideo, tblVideoBox, tblLoan and tblStaff. I need to create a form which the user will use when loaning a video. On this form I want to be able to enter the customer ID and have the address and other customer details appear based on the ID.
I know it's probably simple but I'm at my wit's end, I can't seem to find any info after googling etc..
I know it's an assignment but I'm not asking for the whole thing to be done, if I can get this part working the rest will fall into place.
tablename is the table with the data you want to bring in
fieldname is the field, in tablename, that has the data you want
MatchingFieldName is the field in tablename with the related data (the customer name, for example)
ControlName is the other control, on the form, that has the related data (the customer name, for example)
(check the Help file or MSDN's site for more detail)
Slightly more complex but not requiring code or functions would be to use a sub form with a "Parent/Child" relationship (also in the help files).
Or, create a query that puts all the data together and use that as the recordsource of the form. Then, add a combo box with the wizard turned on; one of the wizard options is a "jump to" feature.
I would be using an unbound form, and in the AfterUpdate event of the ID textbox, do a query to retrieve the customer details using the ID entered.
When you're designing an application like this (in the real world), you definitely want the convenience of being able to enter a customer-number and immediately get the full particulars about that customer. There are two good ways to do this:
Use a function like dLookup() as the data-source for a calculated field; or ...
React to an event such as OnUpdate to run a query that finds the customer and copies the data into the (say...) Order record that you're creating.
In the first scenario, the only authoritative information about the customer is located in, and stays located in, the customer-record. Thus, every single Order has the same information, and if you change the Customer record the change is immediately reflected in every Order, and ... "the Gods of Normalization, forsooth they do smile at thee."
But... the Gods of Normalization never had to run a business, where this order needs to be billed or shipped to that address, "just this one time, it's not the customer's new permanent address" and ... ... "what's the matter, Programmer? Why is your face so pale?"
So the second approach is often more useful, even though it results in redundant data. Consider how commercial programs such as Quicken handle this issue.