Combo and text box dependency

[Sandy]

On my form I have a combo box (cboRepairType) which retrieves its list from
field(1) of tblGrips. Once the selection has been made (AfterUpdate I
presume) I would like the corresponding value from field(3) of tblGrips to
display in a text box (txtSellPrice), (or would another type of display be
more appropriate?)
Can this be done?

Please be gentle I am a novice at this
Sandy

 

[Tom Wickerath]

Hi Sandy,

Yes, it can be done. Use the Afterupdate event procedure, as you have
indicated. In VBA code, the columns are "zero-based", so the first column of
the rowsource would be column(0), the second column would be column(1), etc.
You must include the the corresponding value from field(3) in the rowsource
for your combo box. So, for example, if field(3) from the table was the
second field in the rowsource, ie.

SELECT Field1, Field3 FROM tblGrips ORDER BY Field1

then the AfterUpdate event procudure would look something like this. Note:
When you create a new module, you should see Option Explicit as the second
line of code at the top of the module. If you do not see this, then head on
over to this gem tip to re-configure your Visual Basic Editor (VBE) first:

Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions


Option Compare Database
Option Explicit

Private Sub cboRepairType_AfterUpdate()
On Error GoTo ProcError

Me.txtSellPrice = cboRepairType.Column(1)


ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cboRepairType_AfterUpdate..."
Resume ExitProc
End Sub


Make sure to compile your code before attempting to test this functionality.
From within the VBE, click on Debug | Compile {ProjectName}

You will know immediately if you have a compile error. If the option becomes
unavailable (ie. greyed out) then this is a good sign. It means that your
code compiled without an error.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Sandy]

Thanks Tom,

I don't have time to try it at the moment but I will let you know how I go.
Your explanation does seem easy to follow so hopefully it will be a breeze.

Thanks again in the interim.
Sandy

 

[Sandy]

Sorry to take so long to come back to you Tom; the method you illustrate
works fine.

Can I ask a further question?

How can the following code be adjusted to include the corresponding value
from 'SellPrice' field in the combo box rowsource. And if possible to
include the 'BuyPrice' so I can refer to that too in a similar fashion to
the 'SellPrice' field. And would it work with the code you have already
supplied?

tblGrips has three fields 'GripType', 'BuyPrice', 'SellPrice' other tables
referred to in the 'Select Case' would be similar.

Private Sub cboRepairCategory_AfterUpdate()
On Error Resume Next
Select Case cboRepairCategory.Value
Case "Grips"
cboRepairType.RowSource = "tblGrips"
*****Etc*****

End Select
End Sub

I would like to keep all the 'Select case' tables as separate tables.
Hope this makes sense.

Sandy

 

[Tom Wickerath]

Hi Sandy,

How can the following code be adjusted to include the
corresponding value from 'SellPrice' field in the combo box
rowsource.

Given that you appear to be using a table in the rowsource, ie.
cboRepairType.RowSource = "tblGrips", both the sellprice and buyprice fields
should be available. I suspect that you simply need to change a few
properties for your combo box--increase the column count, and set the column
widths appropriately, in order to see these fields. These properties are
found on the Format tab of the Properties dialog.

However, do you really want to specify a table as the rowsource? The reason
I question this is that you may have difficultly achieving a list that is
sorted properly. Generally, it is better to use a saved query or a SQL
statement for the rowsource. This allows precise control over the sorting,
using the ORDER BY clause.

And would it work with the code you have already supplied?
Yes.

I would like to keep all the 'Select case' tables as separate tables.

May I ask why? Storing similar data in different tables is generally not
considered good database design. You will likely be better off to have all of
the data in one table, with an added field that describes the attribute that
your table’s names are currently describing. Can you please expand on why you
wish to segregate apparently similar data into separate tables? It would be
helpful to provide examples of this other data.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Sandy]

Hi Tom

I thought separate tables would be easier because:

There would be five basic tables:-
tblRepairCategory
with fields:- RepairCatID (Autonumber), RepairCategory (Text)
values being - Grips, Loft and Lie, Reglue, Shafts.

tblGrips
with fields: RepairType (Text), BuyPrice (Currency), SellPrice
(Currency)
typical values for Repair Type:- Gents A, Gents B etc.

tblLoftAndLie
with fields: Repair Type (Text), BuyPrice (Currency), SellPrice
(Currency)
typical values for Repair Type:- Loft, Lie, Loft and Lie.

tblReglue
with fields: Repair Type (Text), BuyPrice (Currency), SellPrice
(Currency)
typical values for Repair Type:- Graphite, Steel, Junior etc

tblShafts
with fields: Repair Type (Text), BuyPrice (Currency), SellPrice
(Currency)
typical values for Repair Type:- Graphite S, Graphite R, Steel S
etc.

I would also have a table for Customers (Usual stuff) and also for
RepairJob.

Repair Job would contain the JobNo, Repair Category, RepairType, Number (of
Units), BuyPrice, SellPrice, SubTotal, Discount, TotalCost. - SubTotal and
TotalCost would be calculated fields.

Naturally there would be a few queries for data analysis.

I thought it would be easy to supply the combo boxes by having separate
tables.

Your comments are appreciated.
Sandy

 

[Tom Wickerath]

Hi Sandy,

I thought it would be easy to supply the combo boxes by having separate
tables.

Not really. It is just as easy to include the appropriate criteria in the
WHERE clause of a SQL statement (or saved query), so that the appropriate
records are selected. You will likely want to base the rowsource on a query
in any case, so that you can apply an appropriate sort order.

But here's just one example of where the difficulty comes in, by storing
like data in separate tables: suppose you need to create a report, grouped by
Repair Type, which includes all of the repair types offered. In this case,
you will be forced into using a Union query, in order to join all the data
back together again. Here is a tutorial on Union queries, in case you are not
aware of this query type. It is on my personal web space, and was written
several years ago by a co-worker at my place of work:

http://home.comcast.net/~tutorme2/samples/unionqueries.zip

So, I think it would be best if you combined the data from tblGrips,
tblLoftAndLie, tblReglue, tblShafts, etc. into one table. Add a new field to
indicate the repair type. This can either be a text field, with values such
as "grips", "loft", "reglue", "shafts", etc., or it can be a numeric field
(Long Integer--->remove the default 0 value) that serves as a foreign key
field for a new table, say tblRepairTypes, with an autonumber primary key.

If you go with the two table design, which is normalized better vs. a single
table design, where repair types are broken out to a new table, you would
likely want to have two combo boxes on your form: one to select the repair
type, and the second (synchronized) combo box to allow the user to select
appropriate records for the repair type selected in the first combo box. Here
are a couple of tutorials on this subject:

Limit content of combo/list boxes
http://www.mvps.org/access/forms/frm0028.htm

How to Synchronize Two Combo Boxes on a Form
http://support.microsoft.com/kb/289670

Note: Disregard the "in Access 2002 or in Access 2003" part from the title
of the second article, as the same logic applies to other versions of Access
equally well.

Here is a link to various database design papers. This topic is very
important that you gain a good understanding of, in order to have success
using Access. I recommend at least reading the first two articles, by author
Michael Hernandez:

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tom Wickerath]

or it can be a numeric field

(Long Integer--->remove the default 0 value) that serves as a foreign key
field for a new table, say tblRepairTypes, with an autonumber primary key.

I see now that the above sentence should have read:

or it can be a numeric field (Long Integer--->remove the default 0 value)
that serves as a foreign key field for your existing tblRepairCategory table,
with an autonumber primary key.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Sandy]

Got it working Tom - Many thanks for your expertise and patience.
Sandy

 

[Tom Wickerath]

Hi Sandy,

You're very welcome. Good luck on your project!


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________