Combo box is creating duplicate records?

[sidetracked]

Ok fellas..I'll try to explained as detailed as i can.

1 Form with a subreport. Mail form is called client, with a subreport or proposal. On the mail form i have a combo box that displays the company name. When i hit the drop down i can choose the company name and all other fiels, address, city, contact etc. autofill in with the correct data. If i choose a company, and on my subreport i enter a new proposal number (primary key). and then hit close. It creates a duplicate company in the client table? So say i choose a compnay name of joe ice cream. and then on my sub report i enter in a proposal number. when i hit save nothing happends..it kinda looks like a refresh. Then when i hit close it closes with no prompt. when i go into the client table it created another company named joe ice cream with the proposal number i entered in. I tried to go into the properties to company name and set it for no duplicates allowed. but everytime i go into the form choose a company with the drop down and then click on the sub report it gives me an error saying duplicate records are not allowed.??

can somone help me out with this..im going crazy here

Thanks!

MCSE 2K - MCSA 2K - NET+ - A+

Paul..

 

[kjv1611]

Could you use a conditional statement in order to test if the value already exists, and if so, have it update the existing record as apposed to adding a new record? Using the Dlookup function would work, I would think for this problem. Something like If DLookup(your info in here) Then...

Of course, you would need to set the criteria part of the Dlookup to basically say "Current Company = Company listed on my form" and put that in Access VBA language, not English. [WINK].


Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[sidetracked]

Well..brainstorming my problem i think i know what my problem is..my combo box is quering information and not pulling from the table..so each record is being received as a new record. On my combobox i have the following code

Private Sub ComboBox_AfterUpdate()

ComboBox.SetFocus

Me.CompanyName = ComboBox.Column(0)
Me.ContactFirstName = ComboBox.Column(1)
Me.ContactLastName = ComboBox.Column(2)
Me.FACILITY = ComboBox.Column(3)
Me.FacilityContact = ComboBox.Column(4)
Me.FacilityPhone = ComboBox.Column(5)
Me.PhoneNumber = ComboBox.Column(6)
Me.Extension = ComboBox.Column(7)
End Sub

I think this is just placing the information in the fields and not using the information. I need to figure out a way to use the information and not ask for a copy of it.

MCSE 2K - MCSA 2K - NET+ - A+

Paul..

 

[PHV]

Take a look in the FAQ area of this forum as an entire section is devoted to combo.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheAceMan1]

sidetracked . . . . .

Enter [blue]proposal number[/blue] first!

See Ya! . . . . . .

 

[sidetracked]

I been trying that and it doesn't seem to work. its creating 1 duplicate record for the company name, then i can enter in info and hit my nifty little save button and keep choosing my same company with the drop down of the combo box and it will keep putting records under the same company. except the first time it creates a duplicate company?

MCSE 2K - MCSA 2K - NET+ - A+

Paul..

 

[TheAceMan1]

sidetracked . . . . .

Forget my last post, accidentally hit submit.

It does'nt sound like your Form & [blue]subForm[/blue] are linked in any way. Are they?

Post the tables (pertinent data only), along with PK's, FK's & relationships. This should should reveal the problem.

See Ya! . . . . . .

 

[sidetracked]

OK, im working with two tables.

"Customer" and "Proposals"

Customer has Primary key value of CustomerID (autonumber)
Proposals has primary key value of Proposal #

Both tables are linked together with a one to many relationship. Join type is 1. only include rows where the joined fields from both tables are equal.
all the check boxes are checked in the relationship properties.

Do i need to modify the record source on the form? cause i just noticed it is only pulling info from the customer table??

MCSE 2K - MCSA 2K - NET+ - A+

Paul..

 

[TheAceMan1]

sidetracked . . . . Almost there . . . .

I assume the join field is [blue]CustomerID[/blue]. Is this correct?

If so, is your subForm linked to Clients through CustomerID via [blue]Master/Child [/blue]link?

Is [blue]CustomerID[/blue] autonumber?

[blue]Do i need to modify the record source on the form? cause i just noticed it is only pulling info from the customer table?[/blue]

No. Your following the logic of your relationship. When you switch to another Customer, you want to see the related Proposals in the subform.

Sorry this is taking so long, but we don't know whats under the hood.

Please answer all questions . . . . .

See Ya! . . . . . .

 

[sidetracked]

First off thank you for taking the time to help me out. I find this site very helpful and try my best to give back as much info as i can.

Thanks again..

QQQQ I assume the join field is CustomerID. Is this correct?
AAAA Yes join field is customerID (sry my mistake i didn't note that)

QQQQ If so, is your subForm linked to Clients through CustomerID via Master/Child link?
AAAA Im not sure what u mean by that? How can I check where it is linked?

QQQQ Is CustomerID autonumber?
AAAA Yes customerID is an autonumber..

QQQQ No. Your following the logic of your relationship. When you switch to another Customer, you want to see the related Proposals in the subform.
AAAA This form that i am using is stricky data entry only. It doesn't need to show information. When i choose the client or (company) I just use this form to enter a new proposal and submit the information.




MCSE 2K - MCSA 2K - NET+ - A+

Paul..

 

[KenReay]

Hi

Way back in your first post you say:

"it created another company named joe ice cream with the proposal number i entered in. "

Since you are refering to duplicate Client Records, why would the Client Record have a column for Proposal number?, is this a typo?

a few posts later:

"Do i need to modify the record source on the form? cause i just noticed it is only pulling info from the customer table?? "

The main form probably does not need to be bound at all, the subform should be bound to the Proposals table.

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[TheAceMan1]

[blue]QQQQ If so, is your subForm linked to Clients through CustomerID via Master/Child link?
AAAA Im not sure what u mean by that? [purple]How can I check where it is linked?[/purple][/blue]

Open form Clients in design view. Select the subform control. In the properties for the subform control, select the [blue]Data Tab[/blue]. There you'll see [purple]Link Child Fields[/purple] & [purple]Link Master Fields[/purple] properties. Anything in there?

See Ya! . . . . . .

 

[sidetracked]

QQQQ Since you are refering to duplicate Client Records, why would the Client Record have a column for Proposal number?, is this a typo?

AAAA Yes this is a typo My client or (customer) form is the main form. and the proposal number is in the subform. which are both two seperate tables

the subform should be bound to the Proposals table.
Yes it is.

There you'll see Link Child Fields & Link Master Fields properties. Anything in there?
Yes both master and child have CustomerID in them

MCSE 2K - MCSA 2K - NET+ - A+

Paul..