Request for guidance on Application freezing--won't stop running code

D

Dan

Hi,

I just got a new job at an electrical adapter manufacturing company,
and this is my first employment as an Access developer. I'm designing
an application for Customer Service agents to obtain price quotes for
part numbers. I'm migrating this from Excel. The boss has specific
design requirements that do not accommodate normalization. We are not
storing any transactions, just presenting prices.

I need a second opinion. What am I doing wrong?

I'm using Access 2000. My application freezes when, in 10 text boxes
on the parent form--frmBuildQuote, I try to sum two text boxes each
from two subforms.

I get the error: "you can't use the design view while code is still
running."

The application hangs and I have to quite by using the windows task
manager.

Table information is at the end of the message.

Thanks

Dan Dungan


-------------------------------------------------------------------------------------------

Here are some details:

In an unbound form, I'm using a combo box to populate 18 text boxes
with the following query as the row source

SELECT tblPriceFormulas.PARTNUM, tblPriceFormulas.FUNCTION,
tblPriceFormulas.SERIES, tblPriceFormulas.CORE,
tblPriceFormulas.CORE_MULTIPLIER, tblPriceFormulas.CONNECTOR_CD,
tblPriceFormulas.ADAP_CONFIG, tblPriceFormulas.SHELL_SIZE,
tblPriceFormulas.CORE_LENGTH, tblPriceFormulas.ENTRY_ADDER,
tblPriceFormulas.ENV, tblPriceFormulas.ENTRY_SIZE,
tblPriceFormulas.CLAMP_NBR, tblPriceFormulas.LEN_OPT,
tblPriceFormulas.PLAT_CD, tblPriceFormulas.MOD_CD,
tblPriceFormulas.[2_PC_ADDER], tblPriceFormulas.BAND_STYLE,
tblPriceFormulas.CRIMP_RING, tblPriceFormulas.KELLUM,
tblPriceFormulas.SELF_LOCK
FROM tblPriceFormulas;


On the combo box after update event I'm using the combo box value as
perameters in the following sql

Private Sub cboPartNum_AfterUpdate()
Dim cSQL As String
If Len("" & cboPartNum) > 0 And Len("" & txtShellSize) > 0 Then
cSQL = cSQL & "SELECT DISTINCT tblPriceFormulas.PARTNUM,
tblPriceFormulas.CORE_MULTIPLIER, tblPriceListCore.CORE_PART,
tblPriceFormulas.CORE, tblPriceListCore.ADAPTER_CONFIGURATION,
tblPriceFormulas.ADAP_CONFIG, tblPriceListCore.SHELL_SIZE,
tblPriceListCore.CORE_LENGTH, [1-9]*[CORE_MULTIPLIER] AS Q1,
[10-19]*[CORE_MULTIPLIER] AS Q10, [20-49]*[CORE_MULTIPLIER] AS Q20,
[50-99]*[CORE_MULTIPLIER] AS Q50, [100-249]*[CORE_MULTIPLIER] AS Q100,
[250-499]*[CORE_MULTIPLIER] AS Q250, [500-999]*[CORE_MULTIPLIER] AS
Q500, [1000-2499]*[CORE_MULTIPLIER] AS Q1000,
[2500-4999]*[CORE_MULTIPLIER] AS Q2500, [5000 & UP]*[CORE_MULTIPLIER]
AS Q5000 "
cSQL = cSQL & "FROM tblPriceListCore INNER JOIN
tblPriceFormulas ON(tblPriceListCore.CORE_PART =
tblPriceFormulas.CORE)AND(tblPriceListCore.ADAPTER_CONFIGURATION =
tblPriceFormulas.ADAP_CONFIG) "
cSQL = cSQL & "WHERE (tblPriceFormulas.PARTNUM)= '" &
Me!cboPartNum & "' AND (tblPriceListCore.SHELL_SIZE)='" &
Me!txtShellSize & "';"
Me!frmCorePrice.Form.RecordSource = cSQL
'Debug.Print cSQL

cSQL = ""
End If

If Len("" & cboPartNum) > 0 And Len("" & txtShellSize) > 0 Then
cSQL = cSQL & "SELECT DISTINCT tblPriceFormulas.PARTNUM,
tblPriceListAdderEntry.ENTRY_ADDER, tblPriceListAdderEntry.ENV,
tblPriceListAdderEntry.ENTRYNUMBER,
tblPriceListAdderEntry.ENTRY_ADDER_LENGTH,
tblPriceListAdderEntry.[1-9] AS QEA1, tblPriceListAdderEntry.[10-19]
AS QEA10, tblPriceListAdderEntry.[20-49] AS QEA20,
tblPriceListAdderEntry.[50-99] AS QEA50,
tblPriceListAdderEntry.[100-249] AS QEA100,
tblPriceListAdderEntry.[250-499] AS QEA250,
tblPriceListAdderEntry.[500-999] AS QEA500,
tblPriceListAdderEntry.[1000-2499] AS QEA1000,
tblPriceListAdderEntry.[2500-4999] AS QEA2500,
tblPriceListAdderEntry.[5000 & UP] AS QEA5000 "
cSQL = cSQL & "FROM tblPriceFormulas INNER JOIN
tblPriceListAdderEntry ON tblPriceFormulas.ENTRY_ADDER =
tblPriceListAdderEntry.ENTRY_ADDER "
cSQL = cSQL & "WHERE (tblPriceFormulas.PARTNUM)= '" &
Me!cboPartNum & "' AND (tblPriceListAdderEntry.ENTRYNUMBER)= '" &
Me!txtEntryNum & "' and (tblPriceListAdderEntry.ENV)='" & Me!txtEnv
& "';"
Me!frmEntryAdder.Form.RecordSource = cSQL
'Debug.Print cSQL

cSQL = ""
Me.Form.Refresh
End If

End Sub

Ok so far.

I have two sub forms that present the sql output.

Then I added more text boxes to

Tables
-----------------------------------------------------------------------------------------------
tblPriceFormulas

Field Name Data Sample
tbl
FUNCTION A
SERIES 01
PARTNUM AXX02B
CORE 217
CORE_MULTIPLIER 1.2
CONNECTOR_CD
ADAP_CONFIG B
SHELL_SIZE
ENTRY_ADDER EXX07
ENV E/N
ENTRY_SIZE
CLAMP_NBR
LEN_OPT X
PLAT_CD
MOD_CD
2_PC_ADDER
BAND_STYLE
CRIMP_RING
KELLUM
SELF_LOCK

--------------------------------------------------------------------------------------
tblPriceListAdderEntry

Field Name Data Sample
ID 50
ENTRY_ADDER EXX07
ENV E
ENTRYNUMBER 03
ENTRY_ADDER_LENGTH
1-9 $7.25
10-19 $6.92
20-49 $6.77
50-99 $6.62
100-249 $6.47
250-499 $6.33
500-999 $5.41
1000-2499 $4.89
2500-4999 $4.63
5000 & UP $4.50

------------------------------------------------------------------------------------------------
tblPriceListCore

Field Name Data Sample
ID 14
CORE_PART 217
ADAPTER_CONFIGURATION S
SHELL_SIZE 08
CORE_LENGTH
1-9 $10.47
10-19 $9.70
20-49 $9.47
50-99 $7.46
100-249 $5.98
250-499 $4.81
500-999 $4.25
1000-2499 $3.67
2500-4999 $3.33
5000 & UP $3.17
 
D

Damian S

Hi Dan,

Have you tried hitting ctrl-break to see where the code is running? If
there really is code running that should place you on the currently executing
line of code.

Damian.

ps: I'm yet to see something where normalisation (at least to some degree)
is not a good idea - just because they want data to display/behave in a
particular way doesn't mean that you need to be constrained in your table
design.

Dan said:
Hi,

I just got a new job at an electrical adapter manufacturing company,
and this is my first employment as an Access developer. I'm designing
an application for Customer Service agents to obtain price quotes for
part numbers. I'm migrating this from Excel. The boss has specific
design requirements that do not accommodate normalization. We are not
storing any transactions, just presenting prices.

I need a second opinion. What am I doing wrong?

I'm using Access 2000. My application freezes when, in 10 text boxes
on the parent form--frmBuildQuote, I try to sum two text boxes each
from two subforms.

I get the error: "you can't use the design view while code is still
running."

The application hangs and I have to quite by using the windows task
manager.

Table information is at the end of the message.

Thanks

Dan Dungan


-------------------------------------------------------------------------------------------

Here are some details:

In an unbound form, I'm using a combo box to populate 18 text boxes
with the following query as the row source

SELECT tblPriceFormulas.PARTNUM, tblPriceFormulas.FUNCTION,
tblPriceFormulas.SERIES, tblPriceFormulas.CORE,
tblPriceFormulas.CORE_MULTIPLIER, tblPriceFormulas.CONNECTOR_CD,
tblPriceFormulas.ADAP_CONFIG, tblPriceFormulas.SHELL_SIZE,
tblPriceFormulas.CORE_LENGTH, tblPriceFormulas.ENTRY_ADDER,
tblPriceFormulas.ENV, tblPriceFormulas.ENTRY_SIZE,
tblPriceFormulas.CLAMP_NBR, tblPriceFormulas.LEN_OPT,
tblPriceFormulas.PLAT_CD, tblPriceFormulas.MOD_CD,
tblPriceFormulas.[2_PC_ADDER], tblPriceFormulas.BAND_STYLE,
tblPriceFormulas.CRIMP_RING, tblPriceFormulas.KELLUM,
tblPriceFormulas.SELF_LOCK
FROM tblPriceFormulas;


On the combo box after update event I'm using the combo box value as
perameters in the following sql

Private Sub cboPartNum_AfterUpdate()
Dim cSQL As String
If Len("" & cboPartNum) > 0 And Len("" & txtShellSize) > 0 Then
cSQL = cSQL & "SELECT DISTINCT tblPriceFormulas.PARTNUM,
tblPriceFormulas.CORE_MULTIPLIER, tblPriceListCore.CORE_PART,
tblPriceFormulas.CORE, tblPriceListCore.ADAPTER_CONFIGURATION,
tblPriceFormulas.ADAP_CONFIG, tblPriceListCore.SHELL_SIZE,
tblPriceListCore.CORE_LENGTH, [1-9]*[CORE_MULTIPLIER] AS Q1,
[10-19]*[CORE_MULTIPLIER] AS Q10, [20-49]*[CORE_MULTIPLIER] AS Q20,
[50-99]*[CORE_MULTIPLIER] AS Q50, [100-249]*[CORE_MULTIPLIER] AS Q100,
[250-499]*[CORE_MULTIPLIER] AS Q250, [500-999]*[CORE_MULTIPLIER] AS
Q500, [1000-2499]*[CORE_MULTIPLIER] AS Q1000,
[2500-4999]*[CORE_MULTIPLIER] AS Q2500, [5000 & UP]*[CORE_MULTIPLIER]
AS Q5000 "
cSQL = cSQL & "FROM tblPriceListCore INNER JOIN
tblPriceFormulas ON(tblPriceListCore.CORE_PART =
tblPriceFormulas.CORE)AND(tblPriceListCore.ADAPTER_CONFIGURATION =
tblPriceFormulas.ADAP_CONFIG) "
cSQL = cSQL & "WHERE (tblPriceFormulas.PARTNUM)= '" &
Me!cboPartNum & "' AND (tblPriceListCore.SHELL_SIZE)='" &
Me!txtShellSize & "';"
Me!frmCorePrice.Form.RecordSource = cSQL
'Debug.Print cSQL

cSQL = ""
End If

If Len("" & cboPartNum) > 0 And Len("" & txtShellSize) > 0 Then
cSQL = cSQL & "SELECT DISTINCT tblPriceFormulas.PARTNUM,
tblPriceListAdderEntry.ENTRY_ADDER, tblPriceListAdderEntry.ENV,
tblPriceListAdderEntry.ENTRYNUMBER,
tblPriceListAdderEntry.ENTRY_ADDER_LENGTH,
tblPriceListAdderEntry.[1-9] AS QEA1, tblPriceListAdderEntry.[10-19]
AS QEA10, tblPriceListAdderEntry.[20-49] AS QEA20,
tblPriceListAdderEntry.[50-99] AS QEA50,
tblPriceListAdderEntry.[100-249] AS QEA100,
tblPriceListAdderEntry.[250-499] AS QEA250,
tblPriceListAdderEntry.[500-999] AS QEA500,
tblPriceListAdderEntry.[1000-2499] AS QEA1000,
tblPriceListAdderEntry.[2500-4999] AS QEA2500,
tblPriceListAdderEntry.[5000 & UP] AS QEA5000 "
cSQL = cSQL & "FROM tblPriceFormulas INNER JOIN
tblPriceListAdderEntry ON tblPriceFormulas.ENTRY_ADDER =
tblPriceListAdderEntry.ENTRY_ADDER "
cSQL = cSQL & "WHERE (tblPriceFormulas.PARTNUM)= '" &
Me!cboPartNum & "' AND (tblPriceListAdderEntry.ENTRYNUMBER)= '" &
Me!txtEntryNum & "' and (tblPriceListAdderEntry.ENV)='" & Me!txtEnv
& "';"
Me!frmEntryAdder.Form.RecordSource = cSQL
'Debug.Print cSQL

cSQL = ""
Me.Form.Refresh
End If

End Sub

Ok so far.

I have two sub forms that present the sql output.

Then I added more text boxes to

Tables
-----------------------------------------------------------------------------------------------
tblPriceFormulas

Field Name Data Sample
tbl
FUNCTION A
SERIES 01
PARTNUM AXX02B
CORE 217
CORE_MULTIPLIER 1.2
CONNECTOR_CD
ADAP_CONFIG B
SHELL_SIZE
ENTRY_ADDER EXX07
ENV E/N
ENTRY_SIZE
CLAMP_NBR
LEN_OPT X
PLAT_CD
MOD_CD
2_PC_ADDER
BAND_STYLE
CRIMP_RING
KELLUM
SELF_LOCK

--------------------------------------------------------------------------------------
tblPriceListAdderEntry

Field Name Data Sample
ID 50
ENTRY_ADDER EXX07
ENV E
ENTRYNUMBER 03
ENTRY_ADDER_LENGTH
1-9 $7.25
10-19 $6.92
20-49 $6.77
50-99 $6.62
100-249 $6.47
250-499 $6.33
500-999 $5.41
1000-2499 $4.89
2500-4999 $4.63
5000 & UP $4.50

------------------------------------------------------------------------------------------------
tblPriceListCore

Field Name Data Sample
ID 14
CORE_PART 217
ADAPTER_CONFIGURATION S
SHELL_SIZE 08
CORE_LENGTH
1-9 $10.47
10-19 $9.70
20-49 $9.47
50-99 $7.46
100-249 $5.98
250-499 $4.81
500-999 $4.25
1000-2499 $3.67
2500-4999 $3.33
5000 & UP $3.17
Click to expand...
 

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

Similar Threads

SQL in vba procedure syntax question. 2
userform control structure strategy help request 0
does this sql capability exist 6
How to loop through multi-selection listbox? 17
select case simplification request 7
VBA with Select Case problems... 9
multiple ranking (sorting), based on diff. criteria 0
Graphing Database Growth Rate 1

Top