Storing the value in another textbox populated from a combo box in a form.

[gilgameshau]

Hello,
I "think" I'm trying to do the same thing as kevin19 and I'm also having trouble. I'm trying to do this in Access 2010 which uses accdb but I've managed to export it to the earlier mdb (which actually eliminated all my calculated fields).
This database is still a work in progress.

In my case I have I have a form frmJobs based upon the table tblJobs.
It has a combo box called cmbFixedPriceJobCode which gets it's values from a table called tblFixedPriceJobs and correctly copies the required value from tblFixedPriceJobs into the correct field of tblJobs.

However there is a second field in the same record in tblFixedPriceJobs that I also want to copy into tblJobs (JobCharge and FixedPrice respectively). This may not follow proper database normalisation practice but I'm allowing for the future where values in tblFixedPriceJobs might change but I don't want historical records in tblJobs to change.

I thought I might use an event procedure on my combo box cmbFixedPriceJobCode so that after a field update some VBA could copy the other field across. But that was where I got unstuck was I'm not aware of the relevant VBA instructions to do this . VBA may or may not be the best way to do this so I'm open to other methods.

Thank you for any assistance.

 

[Mihail]

@gilgameshau

However there is a second field in the same record in tblFixedPriceJobs that I also want to copy into tblJobs (JobCharge and FixedPrice respectively). This may not follow proper database normalisation practice but I'm allowing for the future where values in tblFixedPriceJobs might change but I don't want historical records in tblJobs to change.

Indeed (see bolded text). I used myself this idea in order to keep the history for the products sell prices.

For this I create a table:

tblSells
ID_Customer (Number - FK)
ID_Product (Number - FK)
SellDate (Date)
SellPrice (Number)

In the form I used a bonded textbox to the field SellPrice and I fill this textbox in the After Update event of the ID_Product. This approach allow me to keep the history.

On the other hand, yours calculated fields about job duration are not OK. Do not store in the DB what you can calculate.

 

[gilgameshau]

@gilgameshau
On the other hand, yours calculated fields about job duration are not OK. Do not store in the DB what you can calculate.

I see your point. It has been quite awhile since I used Access and when I came into 2010 this time I saw the calculated fields and just started using them. They are now all removed and converted to VBA calculations in the form.
I have also normalised the tables so that only a single reference key is used between them. This will require some more ongoing effort to ensure historical records are maintained but some VBA in the forms also stops updates of fields that should be unchanged except when creating a new record so I'm happy.

But I still want to display two fields from the combo box on the form in separate fields. My second field is now not a table field but just available on the form.
I thought about duplicating my original combo box, changing the Column Widths to 0cm;0cm;2.016cm and then setting Enabled to No. While this works I'm left with a down arrow on the second ComboBox I don't want to see.
So is there a better way of doing this, or if not, can I make the down arrow on the second combo box disappear.

 

[gilgameshau]

I thought about duplicating my original combo box, changing the Column Widths to 0cm;0cm;2.016cm and then setting Enabled to No.

In hindsight this second combo box is not a good idea.
Yes I need to display the extra field from the combo box on the form. But I also need to have the value of this extra field so I can use it in subsequent calculations. Using the combo box simply gives me the reference key into the second table.
So how do I bring this value across?

 

[gilgameshau]

I have a solution now.
I have my combo box whose value will just contain the key to reference the second table, though it may display a different field depending upon the column widths I have in its Column Widths property.
I also have an unbound text field which will contain the extra field I want to pull from the other table to display.

On my combo box I also have an event for "after update" which after the combo box is changed will pull field I want and sets my text field to the required value.

Private Sub cmbMyComboBox_AfterUpdate()
txtDisplayValue.Value = DLookup("Name of field I want", "Name of table that contains it", "MyKeyField=" & cmbMyComboBox.Value)
End Sub

 

[Mihail]

I have a solution now.

You are the best
I also have an unbound text field which will contain the extra field I want to pull from the other table to display.
This will not store the historical values. Do a try: modify the data in "Name of table that contains it".