Using a Combo Box to navagate and update subforms w/ tabs

[Sethington]

I have Access connected to Oracle though ODBC and I am trying to make of form that uses two combo boxes to update 7 subforms on tabs. All relationship are a 1 to 1.

I have everything working perfectly except that if the combo box reflects a record, the record counter at the bottom still show that it is on record 1. As a result when I change tabs to the next subform I get an ODBC unique key constraint error because it is trying to change my existing keys.

Here is some screen shot to help you see what I'm talking about.

http://www.geocities.com/sethington2002/screenshots/picture1.jpg

http://www.geocities.com/sethington2002/screenshots/Picture2.jpg

 

[VicRauch]

What are you using as your Child and Master link fields for your subforms. If your two combo boxes, Well Name and Well ID are enough to select the correct record for all your sub-forms, then using those two fields (or just one if that will do it) as your Master link field for each sub-form, then as you change your record in one tab, they will all change as long as whatever field(s) in your master form you are using for your Master link field.
HTH,
Vic

 

[Sethington]

The Well ID is the Master and Well Id is the Child in all subforms.

Basicaly what I have is 2 combo boxes that has 2 columns. When you select the Well name it updates the Well Id and vice versa.

 

[VicRauch]

Then, whatever your record source is for you main form, that is where the navigation buttons and record numbers are coming from at the bottom of your form. What are your record source values for the main form, and for the subforms?

 

[Sethington]

The record source for the main for is a table called CWI_WELLS, which is the same table that I am quering for the Wells Subform on the Wells tab.

 

[PHV]

Hopefully the combos you use as navigation tool are UNBOUND.

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

 

[VicRauch]

PHV - Thanks for pointing out about the combo's being unbound.
Sethington - I did not see anything on the main form that needs a table being bound to the main form. So, I would suggest you remove the table from the main form. Then, does everything work the way you think it should, except that the record number at the bottom of your form does not update as you think it should?

If that is the only problem, then removing the table from the main form should solve the problem.
Vic

 

[Sethington]

I tried removing the CWI_Wells table from the main from but then nothing works because the subforms hinge on the Well_ID. So if I remove the record source it gives me tons of errors.

 

[Sethington]

I forgot to mention that I do have the main records bound. I tried to remove them and then they don't update the fields in the subform.

Is there a way to just select a row in the combo box and then get exact record location of that field and then assign it to the main forms record.

So I could select record 3 from the combo box and then the form says go to that record and it would then show I was on record 3 of 2000. This would then make sure that all of my sub forms were all on the right page.

 

[VicRauch]

PHV's comment about the two combo boxes should not be bound. Are they bound? What on the main form is being updated, deleted, just viewed, or added?

The way I see this master form and sub-forms working, is to have each sub-form, as you said you have, linked to the combo box on the main form, for WellID. [red]WellID should NOT be bound on the main form to any table.[/red]

From what you have said is that fields are being updated in your sub-forms from data on the main form. I don't understand what is going on here. It seems that at least one of your tabs has fields that are actually on the main form, rather than that tab(s) being an actual sub-form. Could you please explain the contents of the tabs please, and just where the fields are on your main form that are bound to the table that is bound to your main form. Then, how are the updates set up to update data in the sub-forms?
Thanks

 

[PHV]

Have a mainform bound to your table.
In the header section create a combo following the wizard and choosing the 3rd option.

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

 

[Sethington]

ok I had to get some external help with this one. You guys where 100% right on making the combo boxes unbound. So I did that and then played with it until those were able to work properly by just using a vb statement that would just update the other.

Me.cboWellID = Me.cboWellName
Me.Requery

Keep in-mind that cboWellName has 2 columns

Then on the main form I coudn't remove it's source from CWI_Wells table completely because it needed the well ID for all of the subforms because of the parent-child relationship.

So I changed the page source to only display the value that was select in the combo box.

SELECT CWI_WELLS.WELL_ID
FROM CWI_WELLS
WHERE (((CWI_WELLS.WELL_ID) = forms!frmWellDatabase!cboWellID));

Thanks for your help.