SQL in vba procedure syntax question.

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
 
J

John W. Vinson

There is something wrong so the sql does not return any records.

Are there any suggestions?

Use the debugger to step through the code until you have debug.printed
the SQL string. Then go to the Immediate window and copy and paste the
SQL string into the SQL view of a new query. Does the query return
records? Are the quotes correct?

John W. Vinson [MVP]
 
D

Dan

Thanks, John.

I didn't know how to debug the SQL portion. The quotes weren't right.
I'll work on it.

Dan
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top