By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,486 Members | 2,061 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,486 IT Pros & Developers. It's quick & easy.

IIf to change the row source table

P: n/a
I am writing an inventory database and want to change / filter the
options shown by a Combo Box / Drop down list.

On a single form first the user is to select a "Hardware Item" (Radio,
Pod, Beacon etc - Approx 10 options) from a drop down list and then
selet a "Status" for this item (In Use, Good Spare, Awaiting Testing
etc - 5 options). There is then a "Location" to be filled in on the
same formand this is where I need help / advise.

For each Hardware Type there are a set of "In Use" locations (these
differ depending on hardware type and run to the hundreds). If the
Status is anything other than "In USe" then there is a single,
standard set of locations that covers every hardware type (Store Room,
Workshop etc, 10 or so options).

At the moment every possible location is stored in a single table
which also has a hardware type column. I can easily divide this table
up into hardware caregories with a query if necessary, but if it's
possible to keep it as one table that would be great.

I have tried using an IIf statement in the Row Source box but I don't
know if I am using it incorrectly or am trying to do too much with one
piece of code.

Is there a way to say something like "(If Hardware Type = "Beacon" And
Status = "In Use", reference table "Beacon Locations"), or (If
Hardware Type = "Pod" And Status = "In Use", reference table "Pod
Locations"), else (reference table "All other locations")" ?

I know this is not the proper syntax for the Iif statement, have read
other posts about embedded Iifs, I'm just trying to make my objective
clear.
Hope this makes sense & thanks in advance.

Little Viking

Feb 24 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
The simplest solution would be to use the afterupdate event of the
hardware item or the status list to write the if statements that set
your lookup tables.

Something like this:

If Hardware Type = "Beacon" then
If Status = "In Use" then
location.rowsource = [Beacon Locations]
Else
location.rowsource = [All other locations]
End If
ElseIf Hardware Type = "Pod"
If Status = "In Use" then
location.rowsource = [Pod Locations]
Else
location.rowsource = [All other locations]
End If
Else
location.rowsource = [All other locations]
End If

location.Requery

-tc

On Feb 24, 3:37 pm, littlevikingg...@hotmail.com wrote:
I am writing an inventory database and want to change / filter the
options shown by a Combo Box / Drop down list.

On a single form first the user is to select a "Hardware Item" (Radio,
Pod, Beacon etc - Approx 10 options) from a drop down list and then
selet a "Status" for this item (In Use, Good Spare, Awaiting Testing
etc - 5 options). There is then a "Location" to be filled in on the
same formand this is where I need help / advise.

For each Hardware Type there are a set of "In Use" locations (these
differ depending on hardware type and run to the hundreds). If the
Status is anything other than "In USe" then there is a single,
standard set of locations that covers every hardware type (Store Room,
Workshop etc, 10 or so options).

At the moment every possible location is stored in a single table
which also has a hardware type column. I can easily divide this table
up into hardware caregories with a query if necessary, but if it's
possible to keep it as one table that would be great.

I have tried using an IIf statement in the Row Source box but I don't
know if I am using it incorrectly or am trying to do too much with one
piece of code.

Is there a way to say something like "(If Hardware Type = "Beacon" And
Status = "In Use", reference table "Beacon Locations"), or (If
Hardware Type = "Pod" And Status = "In Use", reference table "Pod
Locations"), else (reference table "All other locations")" ?

I know this is not the proper syntax for the Iif statement, have read
other posts about embedded Iifs, I'm just trying to make my objective
clear.

Hope this makes sense & thanks in advance.

Little Viking

Feb 25 '07 #2

P: n/a
Thanks for that, I gave it a go but when I try the following it gives
an error

"Run-time error 2465 Microsoft Office Access can't find the field '|'
referred to in your expression"

and debug jumps to the line associated with New_Location.RowSource =
[xxx]

Any suggestions?

thanks,

Little Viking
Private Sub New_Status_AfterUpdate()

If Hardware_Type = "Hardwire" Then
If New_Status = "In Use" Then
New_Location.RowSource = [Empty Hardwire]
Else
New_Location.RowSource = [Always Empty Locations]
End If
ElseIf Hardware_Type = "Radio" Then
If New_Status = "In Use" Then
New_Location.RowSource = [Empty Radio]
Else
New_Location.RowSource = [Always Empty Locations]
End If
Else
New_Location.RowSource = [Always Empty Locations]
End If

New_Location.Requery

End Sub

Feb 25 '07 #3

P: n/a
Your RowSource should be a valid SQL Statement.

Create what data you want visible in Query Designer, switch to SQL
view (the icon looks like sql in lower case) and copy the text.
Then paste that into your vba code, where it says [Empty Hardwire]
etc.

eg.

If Hardware_Type = "Hardwire" Then
If New_Status = "In Use" Then
New_Location.RowSource = "Select * from sometable where
EquipmentType ='Empty Hardwire'"
Else
New_Location.RowSource = "Select * from sometable where
EquipmentType ='Always Empty Locations'"
End If
ElseIf Hardware_Type = "Radio" Then
Feb 25 '07 #4

P: n/a
Thank you so much, that is working great!

Little Viking

Feb 26 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.