Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Posts
    11

    Exclamation Unanswered: Need some basic help

    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.

    Thanks in advance.

  2. #2
    Join Date
    Mar 2004
    Location
    Adelaide, Australia
    Posts
    32
    The form you want to do this with, is it bound or unbound to a table, query, etc?

    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.

  3. #3
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Along the same lines, use a function called "DLookup" as the controlsource for the related fields.

    This can be done directly on the form, without the use of code, and will automatically update.

    For example:

    =DLookup("fieldname","tablename","[MatchingFieldName] = " & [ControlName])

    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.

  4. #4
    Join Date
    Apr 2004
    Posts
    11
    Hi, thanks for your replies.

    From what I understand the form is bound to the tblLoan table, as I used the wizard to create it, and when i enter info it ends up in this table.

  5. #5
    Join Date
    Apr 2004
    Posts
    11

    Thumbs up

    Thanks tcace,

    that seems to have done the trick. Now to put it all together

  6. #6
    Join Date
    Oct 2003
    Posts
    706

    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.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •