updating txt boxes with query

dhunter

Registered User.
Local time
Today, 13:45
Joined
Jul 1, 2009
Messages
26
Not sure which place to post this question so I will do it in both forms and vba!

I know pretty much nothing about vba except for playing around so I apologize ahead of time!

I have a form based off of one table. I have two main fields in the table.

Customer = txt
SOW_Num = num

On my form I have two combo boxes. I have two text boxes corresponding to these. When a user selects a customer from the customer combo box the customer txt box gets populated with that customer.

Then the SOW_Num combo box will only display the values for that customer. This also has a txt box that gets populated once a user has made a selection.

So what I would like to do is update a bunch of txt boxes, and some check boxes based on these two selections. Can't use a subform because the users don't like the look of it.

For example:
User selects : Apples Inc. from customer cbo
User selects : 3 from sow_num cbo
now what I want is:
txt box description to show the value from the table based on these to pieces of information. should only be one row in the table that contains this info. same for price, date, etc. I also have 3 check boxes that I would like either be checked or not checked (they are check boxes in the table) based on the user selections.

Sorry if I am dumbing it down to far but I figure its better to be understood then not.

Here is my code so far:

Private Sub cbo_Customer_AfterUpdate()

Me.tbx_Customer.Value = Me.cbo_Customer.Value

Me.cbo_SOW_Num.RowSource = "SELECT tbl_ALL_SOW.SOW_Num" & _
" FROM tbl_ALL_SOW" & _
" WHERE tbl_ALL_SOW.Customer = '" & Me.tbx_Customer.Value & "'"

End Sub
Private Sub cbo_SOW_Num_AfterUpdate()

Me.tbx_SOW_Num.Value = Me.cbo_SOW_Num.Value
Me.Description.Value = "SELECT tbl_ALL_SOW.Description " & _
"FROM tbl_ALL_SOW" & _
" WHERE tbl_ALL_SOW.Customer = '" & Me.tbx_Customer.Value & "'" & _
" AND tbl_ALL_SOW.SOW_Num = Me.tbx_SOW_Num.Value"
End Sub

The very last part only shows the actual sql query it doesn't show the values in the tables. What do I need to do to make it show the value for this field from the row in the table?
 
dhunter,

You could make this much simpler if you can just add the "Description" field to the row source of your cbo_Customer combo box. By adding this field to the row source and then just have it not be displayed, you can then refer the that value by refering to the combo box colum.

To return the value from your combo box you would use
Me.tbx_Customer = Me.cbo_Customer

Then after adding the additional field you can return the value in the new field like this:
Me.Description = Me.cbo_Customer.column(1)

The reason that you would use "column(1)" is that refering to the various columns is what is known as Zero based numbering. The first column is zero, and the next is column 1.
 

Users who are viewing this thread

Back
Top Bottom