Update textbox on form

S

Sandy

The following are events on two combo boxes on a subform "NewJobsSubform",
the first sets the rowsource of the second and also sets a default value in
the second. The second event sets the value in the text box "Sell" to the
price associated with the RepProduct in cboRepProduct - collected from the
table "RepairProducts".

"Private Sub cboCatName_AfterUpdate()
On Error Resume Next

Me.cboRepProduct.RowSource = "SELECT RepairProduct.RepProduct FROM" & _
" RepairProduct WHERE CatName = '" & Me.cboCatName.Value & "' " & _
" ORDER BY RepProduct;"

Me.cboRepProduct = Me.cboRepProduct.ItemData(0)
Me.cboRepProduct.Requery

End Sub"

"Private Sub cboRepProduct_AfterUpdate()
On Error GoTo Err_cboRepProduct_AfterUpdate

Me!Sell = DLookup("SellPrice", "RepairProduct", "RepProduct = '" &
Nz([cboRepProduct], 0) & "'")

Me!Buy = DLookup("BuyPrice", "RepairProduct", "RepProduct = '" &
Nz([cboRepProduct], 0) & "'")

Exit_cboRepProduct_AfterUpdate:
Exit Sub

Err_cboRepProduct_AfterUpdate:
MsgBox Err.Description
Resume Exit_ProductID_AfterUpdate

End Sub"

The problem is that if a user accepts the default value inserted into
cboRepProduct, then the price is not inserted into textbox "Sell", if
however the user selects the drop-down menu and selects any value (even the
one that was default) the correct price is inserted into textbox "Sell".

I know that I can simply delete the line - 'Me.cboRepProduct =
Me.cboRepProduct.ItemData(0)' from the "cboCatName_AfterUpdate" event - but
I would like to keep it if possible.

How can I force the "Sell" textbox to be filled otherwise?

Sandy
 
G

George Nicholson

Once you "pre-set" the value of the combo programatically, simply call its
AfterUpdate.

Me.cboRepProduct = Me.cboRepProduct.ItemData(0)
cboRepProduct_AfterUpdate


I believe the requery is unnecessary. Setting the rowsource automatically
refills/requeries the combo. (Even so, I'd think you would want to Requery
it before setting it to ItemData(0), not after).
 
J

Jeanette Cunningham

Hi Sandy,
I assume you have some code on the AfterUpdate event for cboRepProduct that
sets the value of txtbox Sell.
Change the following code as indicated:
"Private Sub cboCatName_AfterUpdate()
On Error Resume Next

Me.cboRepProduct.RowSource = "SELECT RepairProduct.RepProduct FROM" & _
" RepairProduct WHERE CatName = '" & Me.cboCatName.Value & "' " & _
" ORDER BY RepProduct;"

Me.cboRepProduct = Me.cboRepProduct.ItemData(0)
Me.cboRepProduct.Requery

End Sub"
Click to expand...

"Private Sub cboCatName_AfterUpdate()
On Error Resume Next

Me.cboRepProduct.RowSource = "SELECT RepairProduct.RepProduct FROM" & _
" RepairProduct WHERE CatName = '" & Me.cboCatName.Value & "' " & _
" ORDER BY RepProduct;"

Me.cboRepProduct = Me.cboRepProduct.ItemData(0)
Call Me.cboRepProduct_AfterUpdate()

End Sub"

Jeanette Cunningham





Sandy said:
The following are events on two combo boxes on a subform "NewJobsSubform",
the first sets the rowsource of the second and also sets a default value
in the second. The second event sets the value in the text box "Sell" to
the price associated with the RepProduct in cboRepProduct - collected from
the table "RepairProducts".

"Private Sub cboCatName_AfterUpdate()
On Error Resume Next

Me.cboRepProduct.RowSource = "SELECT RepairProduct.RepProduct FROM" & _
" RepairProduct WHERE CatName = '" & Me.cboCatName.Value & "' " & _
" ORDER BY RepProduct;"

Me.cboRepProduct = Me.cboRepProduct.ItemData(0)
Me.cboRepProduct.Requery

End Sub"

"Private Sub cboRepProduct_AfterUpdate()
On Error GoTo Err_cboRepProduct_AfterUpdate

Me!Sell = DLookup("SellPrice", "RepairProduct", "RepProduct = '" &
Nz([cboRepProduct], 0) & "'")

Me!Buy = DLookup("BuyPrice", "RepairProduct", "RepProduct = '" &
Nz([cboRepProduct], 0) & "'")

Exit_cboRepProduct_AfterUpdate:
Exit Sub

Err_cboRepProduct_AfterUpdate:
MsgBox Err.Description
Resume Exit_ProductID_AfterUpdate

End Sub"

The problem is that if a user accepts the default value inserted into
cboRepProduct, then the price is not inserted into textbox "Sell", if
however the user selects the drop-down menu and selects any value (even
the one that was default) the correct price is inserted into textbox
"Sell".

I know that I can simply delete the line - 'Me.cboRepProduct =
Me.cboRepProduct.ItemData(0)' from the "cboCatName_AfterUpdate" event -
but I would like to keep it if possible.

How can I force the "Sell" textbox to be filled otherwise?

Sandy
Click to expand...
 
S

Sandy

Perfect George

Thank you
Sandy

George Nicholson said:
Once you "pre-set" the value of the combo programatically, simply call its
AfterUpdate.

Me.cboRepProduct = Me.cboRepProduct.ItemData(0)
cboRepProduct_AfterUpdate


I believe the requery is unnecessary. Setting the rowsource automatically
refills/requeries the combo. (Even so, I'd think you would want to Requery
it before setting it to ItemData(0), not after).


--
HTH,
George



Sandy said:
The following are events on two combo boxes on a subform
"NewJobsSubform", the first sets the rowsource of the second and also
sets a default value in the second. The second event sets the value in
the text box "Sell" to the price associated with the RepProduct in
cboRepProduct - collected from the table "RepairProducts".

"Private Sub cboCatName_AfterUpdate()
On Error Resume Next

Me.cboRepProduct.RowSource = "SELECT RepairProduct.RepProduct FROM" &
_
" RepairProduct WHERE CatName = '" & Me.cboCatName.Value & "' " &
_
" ORDER BY RepProduct;"

Me.cboRepProduct = Me.cboRepProduct.ItemData(0)
Me.cboRepProduct.Requery

End Sub"

"Private Sub cboRepProduct_AfterUpdate()
On Error GoTo Err_cboRepProduct_AfterUpdate

Me!Sell = DLookup("SellPrice", "RepairProduct", "RepProduct = '" &
Nz([cboRepProduct], 0) & "'")

Me!Buy = DLookup("BuyPrice", "RepairProduct", "RepProduct = '" &
Nz([cboRepProduct], 0) & "'")

Exit_cboRepProduct_AfterUpdate:
Exit Sub

Err_cboRepProduct_AfterUpdate:
MsgBox Err.Description
Resume Exit_ProductID_AfterUpdate

End Sub"

The problem is that if a user accepts the default value inserted into
cboRepProduct, then the price is not inserted into textbox "Sell", if
however the user selects the drop-down menu and selects any value (even
the one that was default) the correct price is inserted into textbox
"Sell".

I know that I can simply delete the line - 'Me.cboRepProduct =
Me.cboRepProduct.ItemData(0)' from the "cboCatName_AfterUpdate" event -
but I would like to keep it if possible.

How can I force the "Sell" textbox to be filled otherwise?

Sandy
Click to expand...
Click to expand...
 
S

Sandy

Thanks Jeanette - pretty much the same suggestion as George - so it must be
the answer ;-)

Sandy

Jeanette Cunningham said:
Hi Sandy,
I assume you have some code on the AfterUpdate event for cboRepProduct
that sets the value of txtbox Sell.
Change the following code as indicated:
"Private Sub cboCatName_AfterUpdate()
On Error Resume Next

Me.cboRepProduct.RowSource = "SELECT RepairProduct.RepProduct FROM" &
_
" RepairProduct WHERE CatName = '" & Me.cboCatName.Value & "' " &
_
" ORDER BY RepProduct;"

Me.cboRepProduct = Me.cboRepProduct.ItemData(0)
Me.cboRepProduct.Requery

End Sub"
Click to expand...

"Private Sub cboCatName_AfterUpdate()
On Error Resume Next

Me.cboRepProduct.RowSource = "SELECT RepairProduct.RepProduct FROM" & _
" RepairProduct WHERE CatName = '" & Me.cboCatName.Value & "' " & _
" ORDER BY RepProduct;"

Me.cboRepProduct = Me.cboRepProduct.ItemData(0)
Call Me.cboRepProduct_AfterUpdate()

End Sub"

Jeanette Cunningham





Sandy said:
The following are events on two combo boxes on a subform
"NewJobsSubform", the first sets the rowsource of the second and also
sets a default value in the second. The second event sets the value in
the text box "Sell" to the price associated with the RepProduct in
cboRepProduct - collected from the table "RepairProducts".

"Private Sub cboCatName_AfterUpdate()
On Error Resume Next

Me.cboRepProduct.RowSource = "SELECT RepairProduct.RepProduct FROM" &
_
" RepairProduct WHERE CatName = '" & Me.cboCatName.Value & "' " &
_
" ORDER BY RepProduct;"

Me.cboRepProduct = Me.cboRepProduct.ItemData(0)
Me.cboRepProduct.Requery

End Sub"

"Private Sub cboRepProduct_AfterUpdate()
On Error GoTo Err_cboRepProduct_AfterUpdate

Me!Sell = DLookup("SellPrice", "RepairProduct", "RepProduct = '" &
Nz([cboRepProduct], 0) & "'")

Me!Buy = DLookup("BuyPrice", "RepairProduct", "RepProduct = '" &
Nz([cboRepProduct], 0) & "'")

Exit_cboRepProduct_AfterUpdate:
Exit Sub

Err_cboRepProduct_AfterUpdate:
MsgBox Err.Description
Resume Exit_ProductID_AfterUpdate

End Sub"

The problem is that if a user accepts the default value inserted into
cboRepProduct, then the price is not inserted into textbox "Sell", if
however the user selects the drop-down menu and selects any value (even
the one that was default) the correct price is inserted into textbox
"Sell".

I know that I can simply delete the line - 'Me.cboRepProduct =
Me.cboRepProduct.ItemData(0)' from the "cboCatName_AfterUpdate" event -
but I would like to keep it if possible.

How can I force the "Sell" textbox to be filled otherwise?

Sandy
Click to expand...
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

Recording date of change in table 6
if then on after update event of textbox in form 2
Update a Textbox on a Form with Field Value via VB. 6
Putting an autotext entry into a user form textbox 0
looking up fields in a table to compare to a textbox on a form 5

Top