AutoNumber different between Table and Form

[cbearden]

I have a form, its based on a query. That query contains an Autonumber(intID) from a Table(customers). I also have another table(dealers), that uses the ID number as well. Next to the dealer info, I can click the "+" and it'll show all customers pertaining to that dealer.

I've seen some that have had different ID numbers. In the form, one has the ID of 222...while in the Dealer Table it has the ID of 227.

Does anyone know what the problem is?

Also...I have a subform where you can add a dealer to this Dealer Table if it does not exist in the combo box. Here's the code:

If MsgBox("Add New Dealer Number?", vbYesNo, "Warning") = vbYes Then
DoCmd.OpenForm "DealerContact", acNormal, , , acFormAdd, acDialog, NewData
Response = acDataErrAdded
End If

Thanks for the help.

 

[kamfl610]

I need some clarification here. The dealer could have many customers which is the key is the autonumber you use in from the Customer's table? Is the autonumber a dropdown combo box for your dealers form or is it the other way around?

 

[cbearden]

The dealer can have many customers.
The autonumber is the PK for the Customer Table. When I create a new customer, it'll create that autonumber. That's all its used for. I guess the subform info was too much. That combobox contains intDealerNum (PK to teh Dealer table)

 

[kamfl610]

So does each dealer have a unique PK number? What I would tell you to do is have a form that you can add dealers but on the customer's form, create a dropdown box that gives you the list of available dealers from that dealer's table. You can bound the dropdown with the PK & dealer name but the only thing that will be stored on the customer's table stores that dealer's number. As for the subform to add a dealer, on the customer's mainform, I would put a button to add dealer's which would be your subform.

 

[cbearden]

each dealer does have a unique PK. The only thing I don't have that you said, is the Dealer Name in the combo box. I only have the Dealer #.
I'm just confused b/c I don't see why it would do this. The only reason it shows the customers names in the Dealer Table is b/c it has a relationship with the customer table.

 

[kamfl610]

So you have customer numbers in the dealer table too? Seems rather redundant. What I would do is only store the PK of one of the tables to the other table or you're going to have this problem of different numbers.

 

[cbearden]

How do I get rid of it in the Dealers Table?
The only thing I have in the Dealers Table is: Dealer#, DName, Contact, Phone.
The Customer Table info shows up under the each Dealer when, and only when, I click the "+" sign.
It is because of the relationship. I just deleted the relationship and I went into the dealer table and I could not see any Customer info b/c the "+" was gone.