Informational radio buttons

G

gg

If I have 5 unrelated tables with data that I will be concatenating together
once the user selects 1 field from each table, do I need a common field to
link them all together? When I bring all 3 unrelated tables together, the
number of records grows exponentially. An example of my data tables are as
follows: table1 is report_category, table2 is customer_type, table3 is
product type, etc. etc. Each table contains a letter that corresponds to a
name. Table1-report category, has the following data:
L=lead referrals
C=CRM
D=data

Table2-customer type
D=dealer
C=corporate

Table3-product type
T =training
C=Web control
A=advertising

The user selects one letter from each table. Record1 could be: "LDA",
record2 could be: "LCA", etc. I would want to store the data that has been
concatenated.
Would radio buttons be the best display method? And second question, how do
you get all tables together in order to build the concatenated field to store?

Thank you!
 
B

Brian

One helpful piece of info would be whether you want to auto-populate all
these to be used elsewhere, or if these will be combined one at a time as the
user interacts with your program. If the latter, one would make typically
make another table that has a field for each of these, for example:

Table: CombinedStuff
Field: ReportCategoryID (text, relationship to
ReportCategory.ReportCategoryID)
Field: CustomerTypeID (similar relationship to CustomerType table)
Field: ProductTypeID (similar relationship to ProductType table)

On a form whose RecordSource is this new table, have a combo box for each
one so that the user selectes a report category (RowSource: query based on
ReportCategory table; bound column 1, column 1 showing ReportCategoryID,
column2 showing ReportCategoryName), a customer type (similar RowSource from
CustomerType table), and a product type (similar RowSource from ProductType
table).
 
G

gg

Hi Brian,
I created a new table with the combined fields. Do I need to create a
function to concatenate all of the fields before writing back into this new
table? At what point do I concatenate the fields together? Each record in my
detailed subform will have this combined field: repcat&custtype&prodtype.
What would be my primary key?

Thank you!
 
B

Brian

Since I'm not quite sure how the end result (concatenated values) will be
used, it is a little difficult to answer the question.

1. If the individual components would be combined by the user at runtime:

User picks a repcat from a combo box
User picks a custtype from a second combo box
User picks a prodtype from a third combo box

Your code checks to see if this combination already exists in the table. If
so, it notifies the user that this is a duplicate. If not, it creates a
record with the repcat, custtype, and prodtype. (The easy way to prevent
duplicates is to use a multi-key for the PK - select multiple fields and make
those-together-the primary key). Or, you can simply create an additional
auto-number primary key in the table.

2. If, instead, a single, concatenated value will be selected by a user as
part of a larger process, then you will want to write a function that
populates the field for you. I suspect, though, that the #1 option above may
be what you need. If not, let us know how the combined data will be used.
 
G

gg

This concatenated field will only be stored in the subform detailed contract
record (not the main contract header record) and only for informational
purposes at reporting time.

Do I concatenate the fields in a new text field in the properties event ON
CHANGE event? I'm still not clear where to concatenate the values. I tried
Combo263&Combo251&Combo252 in the On Change event procedure, but the value is
not being stored or displayed in the subform. Am I missing a step?

Thank you, Brian!
 
B

Brian

I would make a text box bound to the field where you want to store the
concatenated info. Make it invisible if it is coming from other info on your
form. Then, put this into the BeforeUpdate event of your form:

Form_BeforeUpdate(Cancel as Integer)
ConCatValue = Combo263 & Combo251 & Combo252
End Sub

ConCatValue would be the invisible text box bound to the ConCatValue field
in the table. You will need to make sure to account for the user leaving one
or more of the combo boxes blank (try it to see you results if one or more
are null).
 
G

gg

I added the code below to a field in my subform detail table under the Before
Update property event. The values that are selected in each of the
ComboBoxes display values on the screen and each column 1 field is bound.
Why is my new field not storing any values? It's still blank.

Thanks for your patience. :)
 
B

Brian

1. If it is not storing the value, is the control (invisible text box?) for
the concatenated string bound to the correct field?
2. The BeforeUpdate event fires when you either close the form, move to a
new record, or force the record to save. Thus, it should store it as you
leave the record and be there when you return. If you want to see it as the
user selects the other three combo boxes, move the concatenation code from
Form_AfterUpdate into a Sub:

Private Sub ConCat()
ConCatValue = Combo263 & Combo251 & Combo252
End Sub

Now, in the AfterUpdate of each of those combo boxes, put this: ConCat

Example:

Private Sub Combo263_AfterUpdate()
ConCat
End Sub

If you want, you may just zip up your app and send it to me. That might be
quicker.

brainy
at
pacifier
dot
com
 
G

gg

It worked!!!! Yeeah! Thank you, Brian. Sorry it took me a while to get it.
Thank you for taking the time. Appreciate your help.
 
G

gg

How do you start from #1 if I've already entered several test data records?
I have several tables that are autonumbered and their relationships are
preventing me from making any changes.

Thank you!
 
R

Rick Brandt

gg said:
How do you start from #1 if I've already entered several test data
records? I have several tables that are autonumbered and their
relationships are preventing me from making any changes.

Thank you!
Click to expand...

Delete all records and then compact.

or

Don't worry about starting at 1 (who cares?).
 
Top