I cannot seem to get around this problem. I have a table that contains a text field that holds combinations of numbers and symbols. When I make a new record, I would like to partially type in the number, let Access autoexpand it, and populate this field, as well as name, address, etc. Using the combo box wizard, I can get all the steps to work except instead of populating the new record, Access brings me to a previously entered record. I've been struggling with this for a couple of days, is the answer in making a separate table to hold this information, or is there a simple way to get this to work????
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Using Combo boxes to populate new records 2
- Thread starter cgbaten
- Start date
-
2
- #2
gusbrunston
Programmer
Hi, CG:
I've looked at your thread a couple of times, and I'm not sure what you need.
If this fits, then great:
I have a data entry form that defaults to the next (numeric) ID number to be used as the primary key of the next record. I put it in the property sheet, Data, Default Value:
If you need to default to a combination of alpha and numeric characters as as new primary key, you'd need a more complicated formula for the default.
Good luck. Gus Brunston
An old PICKer, using Access2000
padregus@attbi.com
I've looked at your thread a couple of times, and I'm not sure what you need.
If this fits, then great:
I have a data entry form that defaults to the next (numeric) ID number to be used as the primary key of the next record. I put it in the property sheet, Data, Default Value:
Code:
=DMax("[OwnerID]","tblOwners")+1If you need to default to a combination of alpha and numeric characters as as new primary key, you'd need a more complicated formula for the default.
Good luck. Gus Brunston
An old PICKer, using Access2000padregus@attbi.com
- Thread starter
- #3
Thanks for replying, but that's not quite what I need. Sorry I was not more clear. In a new record, I need to be able to have a user type an alpha numeric value in a combo box. This value is a preassigned number that represents an individual or company. When they type in this number, it should fill the new record with that individual's information (name, address, etc.) What's happening now is that when you add a new record, and type in that number, it moves you to a record that has already been entered! I could add new record 266, but when I enter the value in the combo box it pulls up record 57! It seems to be doing a lookup, and not what I need it to do. The combo box is currently looking up info from the same table as the stored records, will a separate table help me?
gusbrunston
Programmer
Hi.
Sorry that didn't help. If you simply want to avoid displaying previous records, make your form "data entry" in the property sheet, data.
But I think you need more than that. Do you have a primary key that is unique to a company? Is data entry made at random among many companies, or is data entry organized so that one company is accessed, and then numerous entries are made in relationship to that company?
What kind of records are you trying to create? There's not much new under the sun, so if you're working with sofas, ships, or sealing wax, someone's been there before. Knowing what you're involved in will help some of the real gurus who look at the threads on this site give you the advice you need.
Let's say these "companies" are individual kennels in a doggie motel. There is one motel. There are seventeen kennels. Six doggies can be housed in six compartments in each kennel. So there can be a total of 102 puppies in the motel at one time. Information about the one motel is easily kept in one table with a primary key, "MotelID". The kennels are identified and described (colorID, with or without toiletID, etc.) in another table, with a primary key called "KennelID" that is related to the motel through the key "MotelID". Each doggie is assigned to one of the kennels...that record is kept in a third table with a primary key named "DoggieID" that is related to the particular kennel through the primary key for the kennel, "KennelID".
So a new doggie comes to town. You have a main form that lets you select the kennel he's going to share; a sub form on the main form lets you choose a vacant compartment, if you like (another table); then you choose to add a new record, assigning the kennel through a combo box, and a compartment through another, assign a new ID to the hot puppie, who can then be traced through the Motel, the kennel and the compartment.
The rules of normalization tell us that the records in one table should fully describe each type of product, thing, etc. The information thus stored does not have to be repeated (should not be repeated) in each record of particular things in the shop, inasmuch as much of the description of each individual thing can be described simply by referring to the category.
You shouldn't populate the field in the new record with the individual's or company's information that you have stored elsewhere...just the key so that the new dog can go in the right kennel.
Yes, I think you need two tables, one for the company and one for the records that are related to that company. They would be related (in a relational database, what else?) in a one-to-many relationship. The "one side table" would hold the company information, and the "many side table" would hold the records that have a common relationship with the company.
You probably already know all this, anyway... Gus Brunston An old PICKer, using Access2000
padregus@attbi.com
Sorry that didn't help. If you simply want to avoid displaying previous records, make your form "data entry" in the property sheet, data.
But I think you need more than that. Do you have a primary key that is unique to a company? Is data entry made at random among many companies, or is data entry organized so that one company is accessed, and then numerous entries are made in relationship to that company?
What kind of records are you trying to create? There's not much new under the sun, so if you're working with sofas, ships, or sealing wax, someone's been there before. Knowing what you're involved in will help some of the real gurus who look at the threads on this site give you the advice you need.
Let's say these "companies" are individual kennels in a doggie motel. There is one motel. There are seventeen kennels. Six doggies can be housed in six compartments in each kennel. So there can be a total of 102 puppies in the motel at one time. Information about the one motel is easily kept in one table with a primary key, "MotelID". The kennels are identified and described (colorID, with or without toiletID, etc.) in another table, with a primary key called "KennelID" that is related to the motel through the key "MotelID". Each doggie is assigned to one of the kennels...that record is kept in a third table with a primary key named "DoggieID" that is related to the particular kennel through the primary key for the kennel, "KennelID".
So a new doggie comes to town. You have a main form that lets you select the kennel he's going to share; a sub form on the main form lets you choose a vacant compartment, if you like (another table); then you choose to add a new record, assigning the kennel through a combo box, and a compartment through another, assign a new ID to the hot puppie, who can then be traced through the Motel, the kennel and the compartment.
The rules of normalization tell us that the records in one table should fully describe each type of product, thing, etc. The information thus stored does not have to be repeated (should not be repeated) in each record of particular things in the shop, inasmuch as much of the description of each individual thing can be described simply by referring to the category.
You shouldn't populate the field in the new record with the individual's or company's information that you have stored elsewhere...just the key so that the new dog can go in the right kennel.
Yes, I think you need two tables, one for the company and one for the records that are related to that company. They would be related (in a relational database, what else?) in a one-to-many relationship. The "one side table" would hold the company information, and the "many side table" would hold the records that have a common relationship with the company.
You probably already know all this, anyway... Gus Brunston
An old PICKer, using Access2000padregus@attbi.com
- Thread starter
- #5
Gus...
Thank you very much. Your example helped me to realize the answer to my problem. What I was really looking for was an Auotlookup query that enters data automatically in new records. I almost have it completed, with one small problem:
There are now 2 separate tables, 1 that has an ID number for the primary key, and first and last names. This table is called donors. The 2nd has the ID number, and several item fields. This table is called Donations. There is a One-to-many relationship between the ID number fields in both tables that allows a query to pull the information into a new record. This query has the First and Last name from Donors, and the ID number and items field from Donations. I built a form based on the query, and when you enter the ID number it pulls the corresponding record.
The problem is this: I used the combo box wizard to allow you to type or drop down the available ID numbers. When I make a new record, and choose one of the existing ID numbers, The number is duplicated in the drop down list. This is happening because the wizard wants a place to store the data, and I choose the ID number field. How can I prevent this from happening? If I make 3 entries under the same ID number, the drop down shows the ID number 3 times!
Thanks in advance for any suggestions anyone has....
s-)
Thank you very much. Your example helped me to realize the answer to my problem. What I was really looking for was an Auotlookup query that enters data automatically in new records. I almost have it completed, with one small problem:
There are now 2 separate tables, 1 that has an ID number for the primary key, and first and last names. This table is called donors. The 2nd has the ID number, and several item fields. This table is called Donations. There is a One-to-many relationship between the ID number fields in both tables that allows a query to pull the information into a new record. This query has the First and Last name from Donors, and the ID number and items field from Donations. I built a form based on the query, and when you enter the ID number it pulls the corresponding record.
The problem is this: I used the combo box wizard to allow you to type or drop down the available ID numbers. When I make a new record, and choose one of the existing ID numbers, The number is duplicated in the drop down list. This is happening because the wizard wants a place to store the data, and I choose the ID number field. How can I prevent this from happening? If I make 3 entries under the same ID number, the drop down shows the ID number 3 times!
Thanks in advance for any suggestions anyone has....
s-)
Dear cgbaten,
Shouldn't the source for the ID field in the Query be the Donors Table?
If you have the ID field for the Query coming from the Donations Table it will show all the records you have already entered including the Duplicates/Triplicates...
Hope this helps.
Jim
ps I really liked the doggies, I think that is one of the most accessible explanations I've ever heard.
Shouldn't the source for the ID field in the Query be the Donors Table?
If you have the ID field for the Query coming from the Donations Table it will show all the records you have already entered including the Duplicates/Triplicates...
Hope this helps.
Jim
ps I really liked the doggies, I think that is one of the most accessible explanations I've ever heard.
- Thread starter
- #7
Dear Jim,
I tried using the ID source from the Donors table instead, but it would not pull in the name and adress automatically. The ID number in the Donations table is part of the "Many" side of the One-to-Many relationship. The ID number in donors is the "One" side. I guess that is what makes the Autolookup work. I think the answer must be in the setup of the combo box. In the wizard, if I choose "Store the value in this field", I end up with duplicates/triplicates, but the Autolookup works. If I choose "Remember this value for later use", the Autolookup doesn't work.
Thanks again for the suggestions. I'll try just about anything to get this working!
cgbaten
I tried using the ID source from the Donors table instead, but it would not pull in the name and adress automatically. The ID number in the Donations table is part of the "Many" side of the One-to-Many relationship. The ID number in donors is the "One" side. I guess that is what makes the Autolookup work. I think the answer must be in the setup of the combo box. In the wizard, if I choose "Store the value in this field", I end up with duplicates/triplicates, but the Autolookup works. If I choose "Remember this value for later use", the Autolookup doesn't work.
Thanks again for the suggestions. I'll try just about anything to get this working!
cgbaten
- Status
