Sending Combo Autofills to another table

[ethndr]

I have a table of customers with Account Numbers that I wish to autofill:
Account Number
Customer Name
Address1
Address2

Based off the customer Account Number, I call this table tblCustomers.
I have the autofill working off of an unbound combobox querying the Account Number. Im doing this by "=combo2.column()" method
My problem is I need this data to populate another table, when the form is closed. Seems simple enough but my inexperienced access mind is stumped and I desperately need to move on with other aspects of this task.
I appreciate the help in advance.
ethndr

 

[RuralGuy]

It sounds to me like you already have a table with all of the information you need already filled in and keyed on the Account Number. Where are you getting the information to "AutoFill" from?

 

[ethndr]

I appreciate the reply, what I have is a list (a table) of customers by account number, customer name, address.
When a new complaint is logged in, I want to enter there account number and have it populate the account number, customer name and address into a form and after entering the other necessary information save it to a new table. I basicly want to save keystrokes.

 

[RuralGuy]

Pardon me if this is old information but in a normalized database, information *only* exists in one location. All you put in other tables is the AccountNumber or some unique ID. That way if their name or address changes you change it in one table and everywhere it is needed changes.

If you are currently working on say the "ComplaintTable", all you need in this ComplaintTable is the AccountNumber. That can be accomplished almost automatically with a ComboBox.

Say we have a field in the ComplaintTable called Account. We would bind the ComboBox to ComplaintTable![Account] and have the Row Source of the ComboBox be a query of the CustomerTable that includes AccountNumber and Name.

When the user selects the AccountName from the comboBox, the AccountNumber is saved in the ComplaintTable![Account] field.

The ComboBox wizard could do all of this for you.

 

[ethndr]

I agree, however one of the issues (of many) is that the account may change there address, name etc.. so it is important to have the ability to change the complaint table record to reflect the changes and not change the older records. The only way to do that I saw was to have data posted in the table as record at the time the complaint was taken.
The orginal fields are from an old Q&A flatfile data I'm converting. As I reviewed the account information I saw several address changes, one hospital could have 2 or 3 addresses.
Am I looking at this wrong? I think I'll post the db and let the contructive advice begin.
again I appreciate the input, I have bitten off a bit more that I can chew.
eThndr

 

[ethndr]

here is the mess Ive begun.. I would appreciate any input
eThndr

 

[RuralGuy]

Thanks for the update. I can feel your pain. Just so you know, adding any amount of information to this Complaint file with the ComboBox would be a snap. You have *not* bitten off more than all of us can chew! <g>

 

[ethndr]

Please look at the relationships as well, it looks ok to me, it looks reasonable for what we are doing but... I could be wrong. It has been quite awhile since my last access db. <chuckle> this is what I'm doing for my vacation. I enjoy the challenge but Ive got to get past this hurdle.
Thanks again
eThndr

 

[RuralGuy]

Would it be fair to call the "Details" table "ReturnedMerchandise" or maybe "ComplaintIncident"?

Why is it important to know where the Customer was located when they complained the last time?

So much for the pretty darn easy 1st question you asked! <g>

We can get into the more complicated question slowly.

 

[ethndr]

ComplaintIncident would be a better table name than details
The short answer on why it's important is Reglatory Affairs wants it, what they want they get..
Well it was a simple question I thought <g>, once awhile ago (couple of years) I built a flatfile db in access (cringe) and had it autofilling. But ive sleep since then, lots of water under the bridge and such. That db never got implimented and was lost during a puter change.
So last week they place me over complaints and well it's time for a db change.. Q&A is fine for the early 90's but I need improved functionality.
So then I get the wise Idea to do it by the book.. oh boy... Lots to learn.
Thanks for the help
eThndr

 

[RuralGuy]

See if this won't get you started. In the ComboBox AfterUpdate event of Form1 I put the following *tested* code:

Private Sub Combo0_AfterUpdate()

Me.txtCustomerAccount = Me.Combo0.Column(1)
Me.txtCustomer = Me.Combo0.Column(2)
Me.txtAddress1 = Me.Combo0.Column(3)
Me.txtAddress2 = Me.Combo0.Column(4)

End Sub

It is just as easy to place this data in a different (3rd) table at this time if that's what was required.

As to the Relationships in this database, it could use a little work.

I don't really know enough about the operation to make qualified recommendations. I can however pose a few pointed questions for you to think about.

Do you really need a "DetailID" in the Catalog table? I would think VendorID would be more appropriate. Would a Catalog item have more than one vendor?

Why do you have "DetailID" the the VendorID table? I would think you would only need a CatalogID in the "Details" and from there you could easily get to the vendor.

I have quite a few more questions if you want them. This is basic database design and enjoyable for me but we might want to consider doing this off-line from this NewsGroup since it can get quite involved with a lot of back and forth.

So that you know, I only do this for fun.

 

[ethndr]

I appreciate the help, you can email me via my id here. If you like yahoo messager that will work too.

 

[RuralGuy]

your email does not seem to work so I sent a Private message. How about you trying my email?

 

[ethndr]

hmm I tried via clicking on you id then "send an email to ..." my email is ethndr@earthlink.net

 

[LarsUSN]

I came to this site in hopes of finding an answer to this very same problem. I'm new to access (3 days so far... but I learn fast).
I'm using MS Access 2002:
Problem:
Form1 / Table 1: Containts Contact information
Form2 / Table 2: Is a Trouble ticket system which has a dropdown box full of contacts (users).

What I'm looking to do is to pull user Bob smith from the drop down menu and have his location / phone #'s list on Form2/table2.

Any help would be greatly appreciated! Attached is the Database i'm working with. A reply would be awesome but an edit / repost would be even better so I can see the working changes in action.

Cheers,

Lars

 

[RuralGuy]

Hi Lars,

I'm a little busy right now but I'd be glad to look at it. In the mean time here's a question:

Why not just put "Bob Smith's" account number in Table 2? The ComboBox wizard will do all of the work for you!

hth

 

[LarsUSN]

Well I have about 110 contact records stored, and will have an endless trouble ticket log so it just seemed to make more sense to have two forms / tables. one to store the contact information, for use in the trouble ticket set and one purely for the tickets. Now just have to figure out the simplist way to get a couple of the contact fields into the trouble ticket form/table.

 

[RuralGuy]

Hi Lars,

I've looked at your DB and have what I think are useful suggestions. I'm kind of busy right now but I'll get back to you as soon as I can.

 

[RuralGuy]

Hi Lars,

Sorry it took so long to get back. You seem to be duplicating a lot of fields in the two tables. Why can't you just store the ContactID in the Trouble table. No duplication and you can set up the relationship and get to both at the same time with a query.

You might also consider developing your application at 800x600. It is more common.

hth

 

[LarsUSN]

RuralGuy,

The Duplicate fields were put in to store the information from the trouble ticket all in one place for ease in making reports. However if I knew how to get the combo box to propogate information to the text boxes on the form from a differnt table it would be a great starting point for me. Learning as I go - and can't seem to find any examples of what i'm looking for out there - or dont know what to look for when looking for them.

This post:
http://www.access-programmers.co.uk/forums/showthread.php?t=89985

Seems to be almost EXACTLY what i'm trying to do, so at least I'm not the only one. Could you possibly send me a link to something you think is on the lines of what i'm looking for? Thank you very much!