Label to change text to corresponding column.

[Awagenhurst]

Hello World,
I'm REALLY REALLY new to access and to this forum. I don't know if my title explains what I'm trying to do but I'll give it a shot here.

I'm trying to create a car buying database, that will show you the make, model, year, color and if the car is in stock.

I already have the combo boxes that are linked to the database so they list the make and model, but What I'm after is a label or something else that will tell me if the car is in stock or not. So if the user clicks on Saturn SL2 Black it'll automatically tell them if its in stock.

The other thing I was looking into doing is to have the combo boxes only show the cars that are in stock. So if a black Saturn is out of stock, if they would pick Saturn the color will not appear in the drop down list.

 

[pbaldy]

Cascading combos can do part of what you're asking:

http://www.baldyweb.com/CascadingCombo.htm

In order to have a label change colors, you'll need to test whether the select options exist. That can be done with a DCount() or recordset in code. Of course, a cascading combo would be empty if there were no matching cars.

 

[Awagenhurst]

I have the cascading combos already but my issues is the in stock or out of stock portion. I know I can have a in stock combo box and have it filter down but I don't want that. What I'm looking for is to have cascading combo boxes that at the end the label will say if that car or truck is available based on the information in the cascading combo boxes.

Or

If its possible to have the combo boxes only show what is available with out having to have the user put in stock yes. As soon as your in the form view, the combo boxes are already filtered by in stock is true.

 

[arnelgp]

of course you can have a filtered combobox.
just add a where clause on your select rowsource.

 

[Awagenhurst]

This is what I have as a database:

Year Make Model Transmission Price Per day Instock
2000 Saturn SL2 Auto $150 ?

So if the user selects from the combo box Year and picks 2000, make Saturn, Model Sl2, Transmission Auto, and price per day $150. what I want is a label to tell the user that this car is in stock or out of stock. I have all the combo boxes and they filter the way I want them too. I have a table build that has a column if the car is in stock or not, so is it possible to have a label connected to a table.

 

[pbaldy]

I'll get out of the way.

 

[arnelgp]

drag a label control on your form that will show if it is in stock or not.

create a code on the After Update event of your three comboboxes (Year, Make, Model, Transmission).

private sub cboYear_AfterUpdate()
call subIsInStock
end sub

private sub cboMake_AfterUpdate()
call subIsInStock
end sub

private sub cboModel_AfterUpdate()
call subIsInStock
end sub

private sub cboTransmission_AfterUpdate()
call subIsInStock
end sub


' show whether in stock or not in a label control
private sub IsInStock()
dim sWhere as string
Dim bolOk as boolean
if Trim(Me.cboYear & "") <> "" Then
strWhere = "[yearField] = " & Me.cboYear & " And "
bolOk = True
End If
if Trim(Me.cboMake & "") <> "" Then
strWhere = strWhere & "[makeField] = '" & Me.cboMake & "' And "
bolOk = bolOk And True
End If
if Trim(Me.cboModel & "") <> "" Then
strWhere = strWhere & "[modelField] = '" & Me.cboModel & "' And "
bolOk = bolOk And True
End If
if Trim(Me.cboTransmission & "") <> "" Then
strWhere = strWhere & "[transmissionField] = '" & Me.cboTransmission & "' And "
bolOk = bolOk And True
End If
' trim extra " And " at the end
if strWhere <> "" Then strWhere = Left(strWhere, Len(strWhere)-5)

If bolOK Then
If Nz(DLookup("InStockField", "yourTable", strWhere), False) = True Then
Me.labelToShowInstock.Caption = "In Stock"
Else
Me.labelToShowInStock.Caption = "Out of Stock"
End If
Else
Me.labelToShoInStock.Caption = ""
End If
End Sub

 

[Awagenhurst]

SOLVED!!! Label to change text to corresponding column.

Thank you !!!!