combo box entries problem

[visitnag]

Hi

I have created a Form with few text fields attached to a xtable. I am taking a primary field value from ytable through a combo box.
When I select a value through combo the relevant fields of the xtable are not populating.

My aim is to check the xtable primary value through ytable to restrict the primary field values
How to code as soon as the combo is selected it should populate xtable values, if available, otherwise null fields. so I proceed with data entry.

ytable
A
B
C
xtable
A 12.00
B 13.00

(when A is selected from combo, then it should show the 'A' value from xtable, other wise blank.
then user will proceed with data entry to add 'A' to xtable.

Please guide.

Thank you

 

[dhookom]

Typically if you are storing the primary key value from the ytable, you do not want field values from the ytable to also be stored in the xtable. Can you clarify if this is what you want and why?

You might do this if you are selecting a product code and want to store the current unit price. You wouldn’t want to store the product name or category.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[visitnag]

( First of all I am very new to Access, please excuse)
I am storing the values in xtable.

The ytable is like a master table. I am restricting the xtable's entries to ytable primary field.
The same field is a primary field in xtable also.

To be specific:
ytable fields are:
id(primary), field1,field2,blah,blah
combobox is bound to id

xtable:
id(primary), and three more fields. (these are to be stored in xtable)

In the form I have hidden the id text box of xtable.

1. The combo value selection not populating the record details of xtable, if available, in respective text fields.
2. how to code combo to flash a message when the selected id related entry is already available
in xtable(duplicate restriction)?
3. If record is not available then only blank text fields should appear, to proceed with data entry(I have kept default
date as today's date in date txtbox)

so far in my form its giving duplicate error message after going through the entire data entry of xtable fields. To avoid this
I have a subform to show the already available record in xtable. The form is looking cumbersome.

Please guide.

 

[Andrzejek]

Looks to me ytable and xtable have a 1:1 relation (with xtable having three more fields). That begs the question - why?

Why not have just one table:
id(primary), field1,field2,blah,blah, plus the 3 fields from xtable

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[dhookom]

Again, why are you storing duplicate data? Can you provide a justification? There are solutions for limiting the combo box to previously unselected values and using VBA code to update fields based on values in a combo box row source. [tt][/tt]

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[visitnag]

Yes, but actually ytable is a one time creation, yearly once. The xtable is dependent on users input (manually given data).
We are having a query to create a dummy table by updating the ytable with xtable and also adding some(6) calculating fields. This dummy table is used for making some payments to users. The xtable creation is a continuous process.

 

[dhookom]

Since you seem to insist you need to store the values:
Add every field to the combo box row source (ytable) that you want in xtable. They don’t need to be visible. Then reply with the row source SQL as well as the names of the corresponding fields/controls on the form.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

... ytable is a one time creation, yearly once. [...] a query to create a dummy table by updating the ytable with xtable and also adding some(6) calculating fields. This dummy table is used for making some payments to users. The xtable creation is a continuous process.

Looks like an unnecessarily convoluted process that will bite someone some day.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[visitnag]

ok. thank you