D
Dan
I'm working on a project to make a manual pricing process quicker.
Using Access 2000, I have created two tables:
I. tbl_PriceFormulas
Field Name Data Type
ID Autonumber
FUNCTION text
SERIES text
PARTNUM text
CORE text
CORE_MULTIPLIER number
CONNECTOR_CD text
ADAP_CONFIG text
SHELL_SIZE text
ENTRY_ADDER text
ENV text
ENTRY_SIZE text
CLAMP_NBR text
LEN_OPT text
PLAT_CD text
MOD_CD text
2_PC_ADDER text
BAND_STYLE text
CRIMP_RING text
KELLUM text
SELF_LOCK text
Sample table data:
ID FUNCTION SERIES PARTNUM CORE CORE_MULTIPLIER
CONNECTOR_CD ADAP_CONFIG SHELL_SIZE ENTRY_ADDER ENV
ENTRY_SIZE CLAMP_NBR LEN_OPT PLAT_CD MOD_CD 2_PC_ADDER
BAND_STYLE CRIMP_RING KELLUM SELF_LOCK
1 A 02 A02 217 0.2 B
EXX07 E
2 A 01 A01 45_BENT 0 A
EXX07 E
3 A 01 A01 90_BENT 0 R
EXX07 E
4 A 02 A02 217 0.2 S
EXX07 E 2NS
5 A 02 A02 217 0.2 S
EXX07 E 2SP
6 A 02 A02 45_BENT 0 A
EXX07 E 2SP
7 A 02 A02 90_BENT 0 R
EXX07 E 2SP
8 A 03 A03 EXX03 0 S
EXX03 X
9 A 03 A03 EXX03 0 R
EXX03
10 A 04 A04 217 0 S
EXX04 X 2NS
II. tbl_PriceCorePart
Field Name Data Type
ID AutoNumber
CORE PART text
ADAPTER_STYLE text
ENV text
SHELL_SIZE text
1-9 currency
10-19 currency
20-49 currency
50-99 currency
100-249 currency
250-499 currency
500-599 currency
1000-2499 currency
2500-4999 currency
5000 & UP currency
Then, I created a form based on: SELECT [FUNCTION] & [SERIES] &
[ADAP_CONFIG] AS PRTNUM, tbl_PriceFormulas.FUNCTION,
tbl_PriceFormulas.SERIES, tbl_PriceFormulas.CORE,
tbl_PriceFormulas.CORE_MULTIPLIER, tbl_PriceFormulas.CONNECTOR_CD,
tbl_PriceFormulas.ADAP_CONFIG, tbl_PriceFormulas.SHELL_SIZE,
tbl_PriceFormulas.ENTRY_ADDER, tbl_PriceFormulas.ENV,
tbl_PriceFormulas.ENTRY_SIZE, tbl_PriceFormulas.CLAMP_NBR,
tbl_PriceFormulas.LEN_OPT, tbl_PriceFormulas.PLAT_CD,
tbl_PriceFormulas.MOD_CD, tbl_PriceFormulas.[2_PC_ADDER],
tbl_PriceFormulas.BAND_STYLE, tbl_PriceFormulas.CRIMP_RING,
tbl_PriceFormulas.KELLUM, tbl_PriceFormulas.SELF_LOCK
FROM tbl_PriceFormulas
WHERE ((([FUNCTION] & [SERIES] & [ADAP_CONFIG])="ZZZZ"));
Then, I placed 5 unbound text boxes in the form header:
txtFunction,
txtConnectorCd,
txtSeries,
txtAdapConf,
txtPartNum,
Then, I placed 18 text boxes in the detail of the form.
Text Box Name Control Source:
txtFunctionDet, PRTNUM
txt_AdapConfigDet ADAP_CONFIG
I'm only listing two of the text boxes here. If I can get this to work
the rest of the boxes should work.
In txtPartNum after update event I put the following code:
Private Sub txtPartNum_AfterUpdate()
Dim cSQL As String
If Len(txtFunction) > 0 And Len(txtSeries) > 0 Then
cSQL = "SELECT qry_PrtNum.PrtNum,qry_PrtNum.SERIES,
qry_PrtNum.CORE, qry_PrtNum.CORE_MULTIPLIER, qry_PrtNum.CONNECTION_CD,
qry_PrtNum.ADAP_CONFIG, qry_PrtNum.SHELL_SIZE, qry_PrtNum.ENTRY_ADDER,
qry_PrtNum.ENV, qry_PrtNum.ENTRY_SIZE, qry_PrtNum.CLAMP_NBR,
qry_PrtNum.LEN_OPT, qry_PrtNum.PLAT_CD, qry_PrtNum.MOD_CD,
qry_PrtNum.[2_PC_ADDER], qry_PrtNum.BAND_STYLE, qry_PrtNum.CRIMP_RING,
qry_PrtNum.KELLUM, qry_PrtNum.SELF_LOCK"
cSQL = cSQL & " FROM qry_PrtNum WHERE qry_PrtNum.PrtNum = '" &
Me!txtFunction & "" & "" & Me!txtSeries & "" & "" & Me!txtAdapConf &
"'"
cSQL = cSQL & " ORDER BY qry_PrtNum.PrtNum;"
End If
Me.AllowAdditions = False
Me.RecordSource = cSQL
Debug.Print cSQL
End Sub
There is something wrong so the sql does not return any records.
Are there any suggestions?
Thanks,
Dan
Using Access 2000, I have created two tables:
I. tbl_PriceFormulas
Field Name Data Type
ID Autonumber
FUNCTION text
SERIES text
PARTNUM text
CORE text
CORE_MULTIPLIER number
CONNECTOR_CD text
ADAP_CONFIG text
SHELL_SIZE text
ENTRY_ADDER text
ENV text
ENTRY_SIZE text
CLAMP_NBR text
LEN_OPT text
PLAT_CD text
MOD_CD text
2_PC_ADDER text
BAND_STYLE text
CRIMP_RING text
KELLUM text
SELF_LOCK text
Sample table data:
ID FUNCTION SERIES PARTNUM CORE CORE_MULTIPLIER
CONNECTOR_CD ADAP_CONFIG SHELL_SIZE ENTRY_ADDER ENV
ENTRY_SIZE CLAMP_NBR LEN_OPT PLAT_CD MOD_CD 2_PC_ADDER
BAND_STYLE CRIMP_RING KELLUM SELF_LOCK
1 A 02 A02 217 0.2 B
EXX07 E
2 A 01 A01 45_BENT 0 A
EXX07 E
3 A 01 A01 90_BENT 0 R
EXX07 E
4 A 02 A02 217 0.2 S
EXX07 E 2NS
5 A 02 A02 217 0.2 S
EXX07 E 2SP
6 A 02 A02 45_BENT 0 A
EXX07 E 2SP
7 A 02 A02 90_BENT 0 R
EXX07 E 2SP
8 A 03 A03 EXX03 0 S
EXX03 X
9 A 03 A03 EXX03 0 R
EXX03
10 A 04 A04 217 0 S
EXX04 X 2NS
II. tbl_PriceCorePart
Field Name Data Type
ID AutoNumber
CORE PART text
ADAPTER_STYLE text
ENV text
SHELL_SIZE text
1-9 currency
10-19 currency
20-49 currency
50-99 currency
100-249 currency
250-499 currency
500-599 currency
1000-2499 currency
2500-4999 currency
5000 & UP currency
Then, I created a form based on: SELECT [FUNCTION] & [SERIES] &
[ADAP_CONFIG] AS PRTNUM, tbl_PriceFormulas.FUNCTION,
tbl_PriceFormulas.SERIES, tbl_PriceFormulas.CORE,
tbl_PriceFormulas.CORE_MULTIPLIER, tbl_PriceFormulas.CONNECTOR_CD,
tbl_PriceFormulas.ADAP_CONFIG, tbl_PriceFormulas.SHELL_SIZE,
tbl_PriceFormulas.ENTRY_ADDER, tbl_PriceFormulas.ENV,
tbl_PriceFormulas.ENTRY_SIZE, tbl_PriceFormulas.CLAMP_NBR,
tbl_PriceFormulas.LEN_OPT, tbl_PriceFormulas.PLAT_CD,
tbl_PriceFormulas.MOD_CD, tbl_PriceFormulas.[2_PC_ADDER],
tbl_PriceFormulas.BAND_STYLE, tbl_PriceFormulas.CRIMP_RING,
tbl_PriceFormulas.KELLUM, tbl_PriceFormulas.SELF_LOCK
FROM tbl_PriceFormulas
WHERE ((([FUNCTION] & [SERIES] & [ADAP_CONFIG])="ZZZZ"));
Then, I placed 5 unbound text boxes in the form header:
txtFunction,
txtConnectorCd,
txtSeries,
txtAdapConf,
txtPartNum,
Then, I placed 18 text boxes in the detail of the form.
Text Box Name Control Source:
txtFunctionDet, PRTNUM
txt_AdapConfigDet ADAP_CONFIG
I'm only listing two of the text boxes here. If I can get this to work
the rest of the boxes should work.
In txtPartNum after update event I put the following code:
Private Sub txtPartNum_AfterUpdate()
Dim cSQL As String
If Len(txtFunction) > 0 And Len(txtSeries) > 0 Then
cSQL = "SELECT qry_PrtNum.PrtNum,qry_PrtNum.SERIES,
qry_PrtNum.CORE, qry_PrtNum.CORE_MULTIPLIER, qry_PrtNum.CONNECTION_CD,
qry_PrtNum.ADAP_CONFIG, qry_PrtNum.SHELL_SIZE, qry_PrtNum.ENTRY_ADDER,
qry_PrtNum.ENV, qry_PrtNum.ENTRY_SIZE, qry_PrtNum.CLAMP_NBR,
qry_PrtNum.LEN_OPT, qry_PrtNum.PLAT_CD, qry_PrtNum.MOD_CD,
qry_PrtNum.[2_PC_ADDER], qry_PrtNum.BAND_STYLE, qry_PrtNum.CRIMP_RING,
qry_PrtNum.KELLUM, qry_PrtNum.SELF_LOCK"
cSQL = cSQL & " FROM qry_PrtNum WHERE qry_PrtNum.PrtNum = '" &
Me!txtFunction & "" & "" & Me!txtSeries & "" & "" & Me!txtAdapConf &
"'"
cSQL = cSQL & " ORDER BY qry_PrtNum.PrtNum;"
End If
Me.AllowAdditions = False
Me.RecordSource = cSQL
Debug.Print cSQL
End Sub
There is something wrong so the sql does not return any records.
Are there any suggestions?
Thanks,
Dan