2 combo 1 row 1 column = 1 problem

[tayse]

Hi,
It may be simple but I could not figure out a way to take care of it.

Simply the problem is...
2 combox on a form, the value in 1st box will give me the row number and the second combo box will define the row name, so that the value can be sent to a text box.

Propably this can be done by a simple SELECT statement or DLOOKUP or maybe using arrays (I saw a cell definiton here

http://www.tek-tips.com/viewthread.cfm?qid=770158

by TonyJollans ).

P.S. by SELECT, if I would know how to make this right, that would solve the problem I think.
It should be something like this.

SELECT "rowsname" = cmbrowname, "table", where styleid=cmbtyleid

I know the code has errors but I do not know how to correct it.

Thanks in advance...

 

[MajP]

It may be simple, but I did not understand what you are asking. Why do you need two comboxes to define a row name and a row number? Could this come from one combo box? What is the row source of these combo boxes. Maybe give and example of what is in the combo boxes. If it can be done with one combo box with two columns you could do something like

Private Sub Combo2_Change()
  Dim intRowNumber As Integer
  Dim strRowName As String
  intRowNumber = Combo2.Column(0)
  strRowName = Combo2.Column(1)
  txtBoxOne = "Row Number and Name: " & intRowNumber & " " & strRowName
End Sub

But you will have to explain this better.

 

[tayse]

Hi MajP,
Thank you for your both answers. Here a little example.
I have 200 carpets which come in different size. There are 16 different size and not all carpets have all size.

I have a PriceSize table which is like:

CarpetID | 2'x4' | 2'x8' | 4'x6' | ...
-------------------------------------------
1024 | $20.00 | $0.00 |$54.00 | ...
1043 | $36.00 |$42.00 |$68.00 | ...
1047 | $28.00 |$34.00 |$0.00 | ...

So, first combo defines the carpetID (row number) and second defines the size (column number = field name)and combination of both gives me the UnitPrice for the UnitPrice text box.
I tried to use SELECT but could not make it work.

I will try the code you sent and post the result here.

 

[PHV]

Have a look here:

http://r937.com/relational.html

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

 

[tayse]

I tried this one...

=DLookUp("[3’11'’ x 5’7’’]","idprice","[StyleID] = " & cmbStyleID.Column(0))


it worked in an unsufficient way.... what I need to do is assigning a variable instead of "[3’11'’ x 5’7’’]".
as "[3’11'’ x 5’7’’]" = variable .... but I do not know how to do that, I am not even sure if it is possible.

Appreciate for any help.

 

[PHV]

Something like this ?
strColName = "[3’11'’ x 5’7’’]"
...
curPrice = DLookUp(strColName, "idprice", "[StyleID] = " & cmbStyleID.Column(0))

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

 

[tayse]

Actually more like...

txtUnitPrice = DLookup("cmbsize.column (0)", "SizePrice", "[StyleID] = " _
& cmbStyleID.Column(0))

in cmbsize combo box there are 16 different size. So, I want DLookUp to look for the field that has the same name with combo box value.

thanks for the effort

 

[PHV]

You may try this:
txtUnitPrice = DLookup("[" & cmbsize.column (0) & "]", "SizePrice", "StyleID=" _
& cmbStyleID.Column(0))

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

 

[tayse]

I think it worked but there is another problem somewhere else. Because I get this when I change the value in the combo box.

"Run-time error '2001'
You canceled the previous operation. "



This is exactly what I have now:

Private Sub cmbSize_AfterUpdate()

txtUnitPrice = DLookup("[" & cmbSize.Column(0) & "] ", "SizePrice", "[StyleID] = " _
& cmbStyleID.Column(0))

End Sub

Private Sub cmbStyleID_Click()

Me.Form!txtDesign = cmbStyleID.Column(1)
Me.Form!txtColour = cmbStyleID.Column(2)

End Sub