update text box based on column 1 of selection in combo box 1

[cdck]

I am creating an unbound form with 2 fields and a command button.

The first field is a combo box (cbPart), which lists the ID (hidden), Part#, and Description from a Parts table. The second field is a text field (inputPartQty) that needs to be used for the Quantity. The command button (cmdUpdateQty) will update the record in the table for the selected part (cbPart) based on the number entered in the quantity (inputPartQty).

Once a user selects a part in cbPart, I need the form to refresh, select the Qty required for that part from the parts table, and use it as the default value in inputPartQty. The user will then edit that number, if needed, and hit cmdUpdateQty. cmdUpdateQty will then trigger an expression that will update the QtyOnHand field in the table by adding the amount in inputPartQty.

I am not used to dealing with unbound forms. I cannot figure out how to make inputPartQty fill in once the user selects a Part in cbPart. (Eventually, there will be a second set of controls to deal with assemblies, which are in another database.)

Can anyone help?

Cheryl dc Kern

 

[ZPBJ]

Hi Cheryl, I believe this can be addressed in two parts.

Step 1: Set your textbox to read the input ie =[forms]![cheryl]![Combo1]

Step 2: Go into the properties of Combo1 and on the Event tab click the elipsis next to OnClick and Choose MacroBuilder in the action select Requery and in Control Name type Text1 (or whatever your textboxname is). Save the macro and test it.

__________________________________
Remember that time when I took the box? - Peter Griffin

 

[ZPBJ]

Then you can write a query that will use the [forms]![cheryl]![combo1] as a variable and return only the default value for that specific item

__________________________________
Remember that time when I took the box? - Peter Griffin

 

[cdck]

ZPBJ:

Thanks for your quick attention. I'm not sure that I understand, so let me verify that I'm translating correctly, and then I have a couple questions.

By Combo1, are you referring to the combobox defined above (cbPart)?

The combo box now includes 4 columns, which are 1 ID (hidden), 2 part number, 3 description, and 4 QtyReq. I need the default value of the text box to be the Qty required, which is column 4 in the combo box. If I set the text box to be the same as the combo box, won't it include all 4 columns from teh combo box? How can I tell it to just take comlumn 4?

When I go code the command button, it will need to update a field in the table based on the text boax and column 1 of the combo box. Again, how do I tell it which column to look at?


Cheryl dc Kern

 

[PHV]

In the AfterUpdate event procedure of cbPart:
Me!inputPartQty = Me!cbPart.Column(3)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cdck]

PHV

I think this gets me very close - it seems to be exactly what I want in intention. However, I keep getting an error. I had to remove the "Me"s, so I replaced them by using the expression builder to call the two fields. This is what I got:

=[inputPartQty]=[cbPart].[Column](4)

However, now when I change the item selected in the part list combo box, othing happens. I tried removing the =, but then it tried to find a macro to run. Any other suggestions?

Cheryl dc Kern

 

[TheAceMan1]

How are ya cdck . . .

Note: Column index starts at zero! . . . Now follow this:
[ol][li]Remove what you have in the [blue]Control Source[/blue] property of [blue]InputPartQty[/blue].[/li]
[li]In the [blue]AfterUpdate[/blue] event of the combobox, copy/paste the following:

[blue]Me!inputPartQty = Me!cbPart.Column(3)[/blue]

[/li]
[li]In the [blue]Click[/blue] event of [blue]cmdUpdateQty[/blue], copy/paste the following:

[blue]   Dim SQL As String, ctl As Control
   
   Set ctl = Me!InputPartQty
   
   If IsNumeric(ctl) Then
      SQL = "UPDATE tblParts " & _
            "SET QtyReq = QtyReq+" & ctl & " " & _
            "WHERE (ID= " & Me!cbPart & ");"
      DoCmd.RunSQL SQL
   End If

   Set ctl = Nothing[/blue]

[/li][/ol]
[purple]Thats it! . . .[/purple]

See Ya! . . . . . .

 

[cdck]

Access doesn't appear to be understanding the "Me" reference. When I run the afterUpdate with it set this way, it throws an error saying that Me doesn't refer to anything that it understands. If I build the same expression in the expression builder, it no longer throws an error, but nothing happens when I change the selection.

I was able to get the qty of the first item in the combo box to show up in the Qty box by putting

=[cbPart].[Column](3)

into the default value setting of the control. However, though it does not throw an error anymore, it also does not affect the contents of the Qty input when the combo box selection changes.

I am using Access 2000. Could this be part of the issue, or am I getting paranoid?

Cheryl dc Kern

 

[PHV]

In the AfterUpdate [highlight]event procedure[/highlight] of cbPart:
Me!inputPartQty = Me!cbPart.Column(3)

Tip: click the ellipsis (...)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheAceMan1]

[blue]Access doesn't appear to be understanding the "Me" reference. [purple]When I run the afterUpdate with it set this way, it throws an error saying that Me doesn't refer to anything that it understands.[/purple][/blue]

Apparently [blue]your not incorporating the code in the event[/blue] (in the code module), [blue]but rather on the event line![/blue]
Put the cursor on the [blue]AfterUpdate[/blue] event line, then click the 3 elipses

just to the right. This is where the code goes. Should look like:

[blue]Private Sub [purple][b]ComboxName[/b][/purple]_AfterUpdate()
   [purple]Me!inputPartQty = Me!cbPart.Column(3)[/purple]
End Sub[/blue]

See Ya! . . . . . .

 

[cdck]

THANK YOU SIR!

A very important step that I overlooked. Used the drop-down on the event to set it to [Event Procedure], then hit the ... and got what I needed. I'd tried hitting ... before, but just kept getting the expression builder, which naturally didn't do the job.

I appreciate everyone's help with this!

Cheryl dc Kern