Transferring value from Combo Box 1

[errolf]

When I use a combo box on a form to transfer a value to an update query the value transferred appears as a small square and not the value in the combo box.
Combo box code SELECT T_PICK_STATUS.NO_STATUS, T_PICK_STATUS.ID_STATUS
FROM T_PICK_STATUS;

Command Button code Private Sub CmdStat_Click()
DoCmd.RunSQL "UPDATE T_PICKING_LINES SET T_PICKING_LINES.STATUS = [Forms]![AbyssChange]![ID_STATUS_Label]" & _
"WHERE (((T_PICKING_LINES.PICKED_DATE)=[Forms]![AbyssChange]![Combo8]));"
End Sub
In the table this is the result that appears in. The Expr1 Field Appears as a small square 
The Expr1 Field format is set to number

NO_STATUS ID_STATUS Expr1
0 Unassigned
1 Assigned
2 Deferred
3 Picking List Done
4 Labels Done
5 Confirmed
6 Invoiced
7 Consignment Printed
9 Closed

Can anyone help with this problem

Errol Featherstone

 

[PHV]

IF the control ID_STATUS_Label is a Label you may try this:
[Forms]![AbyssChange]![ID_STATUS_Label][highlight].Caption[/highlight]

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[willir]

Errol

WHERE Clause...

What is the bound field in the combo box?

=[Forms]![AbyssChange]![Combo8]

...will use the value of the bound column which could be NO_STATUS or ID_STATUS based on the information supplied.

Use the properties window to see which column is bound.

Regardless,
WHERE T_PICKING_LINES.PICKED_DATE=[Forms]![AbyssChange]![Combo8]
does not appear to be a Date.

And remember, if working with dates, the date has to be encapsulated with the octophorp or pound sign, "#"


SET Clause

SET T_PICKING_LINES.STATUS = [Forms]![AbyssChange]![ID_STATUS_Label]"

As per PHV's comments, do you mean ID_Status or ID_STATUS_Label

Am I missing something here?

A short cut...
Instead of using [Forms]![AbyssChange]![FieldName] (from the expression builder??), if you are coding for the current form, you can use Me.FieldName

 

[errolf]

Thanks For the help. I changed the code and combo box names to: Private Sub CmdStat_Click()
DoCmd.RunSQL "UPDATE T_PICKING_LINES SET T_PICKING_LINES.STATUS = Format([Forms]![AbyssChange]![CmbChng],'0')" & _
"WHERE (((T_PICKING_LINES.PICKED_DATE)=[Forms]![AbyssChange]![Combo8]));"
End Sub
This works however I dont know if this is the correct way to do it.
Thanks again

Errol

 

[willir]

Errol

I am glad it works.

If you are wondering if it is correct, take a small chunck of data, work out your expectations. Then see if the expectations matches your actual results.

BTW...
This is a pretty nifty bit of code in your SQL statment
Format([Forms]![AbyssChange]![CmbChng],'0')