Problem with Form and controls in Access 2007. SOS!!!

[katrina11]

Hi experts,
I am new with Forms. However I never thought it would take me so much time and efforts.

I have the following tables:
Provider (ProviderID, ProviderName, ProviderAddress)
Member: (ProviderID, MemberName, MemberOrder)

1.Merged 2 tables by Providerid field and created a table TestTbl
2.Created “Data Entry” Form
with RecordSource = TestTbl
3. Created comboBox "PROVIDER" which should allow user to chose Provider by Name or by ID (RecordSource = provider)
4. Created text box “ADDRESS” to get a ProviderAddress while chose a provider Name (RecordSource = provider)
5. Created a combobox "MEMBER" to get all members for the certain provider ( RecordSource = member)

Now I get a mess. It looks like each time I chose a provider from combobox I lose this provider and besides it mess my initial Provider Table because it is a “part” of table TestTbl.

I tried to make a copy of Provider named ProviderAlone and created combobox RecordSource= ProviderAlone . After that my combobox is safe but….it is not supposed to be like that…
Besides, whatever I do I get all the same address!

I spent a whole day yesterday and I am still here.. Member combo box also works incorrectly.
What I am doing wrong? I really need a help….

Would appreciate any help.
Katrin.

 

[randy700]

The first thing I'd do would be to add a primary key to your Member table.
Then, create a query with your two tables linked (ProviderID) and use the query as the record source for your form.

Text boxes and combo boxes have a Control Source, not a Record Source.
The Control Source would be the FIELD associated with that control.


Randy

 

[katrina11]

Randy thank you for the response. Do you mean ProvidedrID as a primary key or just create Primary key on any field (as my Providerid is a text field....)

 

[katrina11]

Also member table looks like:

providerid membername memorder

1111 sveta 1
1111 greg 2
1111 gaetan 3
2222 parna 1
2222 helen 2
2222 john 3
3333 chris 1
3333 mike 2


In other words, providerid is not a good candidat for primary key due to the duplicate values........

What do you think?

 

[PHV]

membername seems a good candidate ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[katrina11]

I will try membername...

At this time I have just created additional autonumeric pk field "PseudoKey". Now combo box selection in
some way has some impact on Address textbox but just in one way.

Whenever I try to be back to the previous combo box choice, I got an error message about trying do some actionwithout Addnew or Edit function.

I am not sure how to code the event.....

 

[katrina11]

Try as I might I was unable to apply Addnew ord Edit methods...

Could anybody please kindly give me an example how to use AddNew and Edit methods to be able to select options in the combobox back and forth without gettting an error?

Thank you for the support,

Katrin

 

[randy700]

membername seems a good candidate
Only assuming you will never have another member with the same name (such as john).

I would add a new field, memberID. Autonumber should work.


Randy

 

[katrina11]

It looks like it works with autonumber but when I had chosen each combo box options one after another and had decided to get back
to the first item system generated an error

" You tried to call Update or CancelUpdate or attempted to update a Field in a recordset without first calling AddNew or Edit. (Error 3020)"

Besides I got a mess in the Form recorsource table.

What I am supposed to do to fix it?

Thank you so much for the support.

Katrin

 

[PHV]

Which line of code is highlighted when the error raises and you hit the Debug button ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[katrina11]

I did not get any code highlited and I am not sure how to get it but an only line I coded on my own was:

Private Sub cmbPrv_Click()
Form.txtPrvAddr = Form.cmbPrv.providerSiteAddress
End Sub

When I tried to escape the application system was warning that Access is trying to save data (although it never happened) which would have been lost otherwise. Asking if i was going to leave anyway..... After I had left, RecordsSource table of Form was totally messed out



w