form is duplicating table records 2

[blaine011]

I have a database which tracks sports tickets that my company gives to clients

There are five tables, invoice (main), and then salesperson, tickets, customer and authorizing person, each with a main ref key pointing back to the corresponding foreign key in the invoice table through relationships.

I've created a form to enter new invoices for each new set of tickets given out, which pulls names from the other tables using a combo box so that duplicates for salespersons, customers, etc aren't created. However, everytime I create a new invoice, it duplicates the customer, salesperson, etc.. selected from the different tables using the combo box. How do I make sure that a combo box is only being used for lookup, and not update the table?

 

[BNPMike]

It looks as though you have availed yourself of Access's autolookup feature.

How do I make sure that a combo box is only being used for lookup, and not update the table

This is confusing. Why would you want to see the correct data but not want it in the table? Then again why do you think it is going into the table - have you checked it before and afterwards?

The bottom line here is it's most likely nothing is actually going wrong. It just looks like it.

 

[blaine011]

for example, I create a new ticket, and one of the combo boxes looks up the names of the owners, joe and bob, so for this example I pick Bob, now I want it to simply add the ref key for bob into the invoice number, which it does, but it also creates another record under the owner table so now instead of only have one Bob and one Joe, there are 2 Bobs. Which defeats the pupose of normalizing the database.

So what could be causing it to duplicate the record in the other tables?

 

[BNPMike]

There is obviously something wrong with the way you have set this up but now I see your question. I'd look at the enabled and locked properties.

The way I do it is populate the list box (You don't want a combo box do you?) with a suitable query then bind the relevant column to the field in the table you are trying to update. I suspect you've got one or two of these settings crossed.

 

[blaine011]

The nice thing about the combo box is the pull down menu, or can I do that with the list box?

 

[BNPMike]

A combo box is a list box that allows you to add things. You don't want to add anything.

 

[blaine011]

but wouldn't it only add things if I typed in something that wasn't in the pull down list? Or am I way off here?

 

[BNPMike]

That's correct except the way you've got it set up you can add duplicates. I suspect you have an autonumber key so you can add Bob and it's not a duplicate because he's got a new autonumber that you may not even be able to see. So until you've got your form sorted out, don't let people add new salesmen etc. Make them do that on another form for that purpose.

 

[MelissaL]

Thank you for posting the answer to this problem. I was also having difficulty with the combo list and it adding records. Here's how I went about fixing it (Thanks to BNPMike's post, of course!):

1. Opened up my relationships
2. Deleted the first relationship line, then right-clicked the form in question to open it.
3. Changed the values I did not want duplicates for to "No" or "Yes (No duplicates)"
3. Saved the form and readded the relationship.
4. Repeat steps for appropriate tables.

I then opened up my original form and tested the combo boxes to make sure it was only pulling the values that I wanted without readding them back into the tables. It worked like a charm. You guys have saved me a ton of hair-pulling and now I can focus on the next problem!

btw, Blaine011, I too prefer the look of the drop down box vs. the list box.

 

[ESquared]

I wish Access comboboxes could be told to "not allow text entry," not just "limit to list." Then they'd behave like a single-select listbox, but be in compact form. Any typing in the field would jump only to items in the list. Typing that didn't match items would do nothing.

 

[blaine011]

MelissaL, how did you setup your combo boxes, because I did the same thing you did, and when I pointed the combo box to access the lookup tables it gave me an error message.

The error message said "The changes you requested could not be made because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index or redefine the index to permit duplicate entries and try again."

 

[MelissaL]

blaine011,
It sounds as though you might have updated one table but not the other, or possibly still had them linked in the Tools...Relationship window??

Here's what my setups look like...

On access form design window:
Combo Box:

Data Tab:
Row Source Type: Table/Query
Row Source: To/Receiver(Insert your Name of Table or Query)
Bound Column: 2 (change to display whichever you want)
Limit to List: Yes
Auto Expand: Yes
Enabled: Yes
Locked: No


Be sure to delete any linked tables in Tools..Relationships first, or Access won't let you update the field.

1. Open 1st table in question:

*Note: My Field in question is not the primary key
(I use a system generated PK that's not on my forms)

2. Click on appropriate field and then on the General Tab:
Indexed (No Duplicates) OR No

3. Save the table

4. Open up the other table(S) and follow steps 1-3, making sure that they match what is on the linked field on the other form.

5. Re-add the relationships between the updated tables.

Hope this helps!

 

[blaine011]

unfortunately that didn't work. even with the relationships deleted, when I ran the form it still tried to duplicate the field, and returned the same error. Also, without the relationship, it won't update the foreign key fields in the main database as they are not liked to the lookup tables anymore.

 

[blaine011]

Does anyone know how to fix this? I just want the lookup table to be used for just that, looking up. But I want the unique identifier field in the lookup table (an autonumber), to be used to update the foreign key in the main table.

So. Aside from the other fields, the user will use a pull down field to choose from a list of names in the lookup table. Based on the name they pick in the list, the corresponding identifying number in the same lookup table, will be inserted into the main table record.

If someone can, I will upload the database to them so that they can look at it and give me a suggestion. I already sent it to one user, but he never replied.

Thanks for all help in this matter.

 

[ESquared]

Set the control source of your listbox or combobox to be the field you want the value stored in.

Set the record source to be a query that returns two columns: first column is the key/value, second column is the displayed value the user sees.

Set the column count to 2. Set the key column to 1. Set the column widths to 0";1" (hide the first column, make the second one an inch wide).

A very elementary thing that is done in forms all the time.

 

[blaine011]

Thanks for the help it worked out great, although the condescending comments are not necessary.

 

[ESquared]

I apologize for any condescension.

Populating a listbox IS elementary. I grant that if you don't know something, well... you don't know it, so you have to learn it the first time, somehow. So why was I snippy?

I have the suspicion that this forum was your first resort instead of your last one. That is... were you spending our time over the last 7 days waiting for us to generate the help, or were you *actively* searching the web or other sources yourself, spending your own time? Am I wrong?

Did you ever try using the Wizard that comes up when you drop a new combobox control on your form? The hint "hide key column (recommended)" might have done the trick.

Did you search tek-tips? Click on the FAQs tab at the top of this page.

Comboboxes - Using Multiple Columns faq702-4284

And this one would have done it, too:

Combo Box - dependant on another Combo Box faq702-4289


Anyway... next time I'll keep the comments to myself and just point people to the FAQ. That alone should get the point across.