Multiple Record Query For Statement

[mcadle]

All,

I am having extreme trouble. I have to build a graphical system to quickly determine the status of a vehicle fleet. I have a table with the Vehicle Id and a yes no for status. I am querying the table and returning these two items. My problem is the code only works for the first record and none of the following records. I have tried for statements and added a incremental field, but to no avail.

My idea was to have a form with multiple command buttons (i.e. 40 of them) and on click opens an additional form where you can adjust the properties of that vehicle. That portion works. The code is in form on open where it would change the caption from red - in service=false (i.e. out of service) and blue - in service = true (i.e. usable) So that way you could look and quickly tell which vehicles were not usable. Here is the code for the form on open:

<code>
If [Vehicle Number] = "e1016" And [In Service] = True Then
Command2.ForeColor = vbBlue
Else
Command2.ForeColor = vbRed
End If
</code>

This works since "e1016" is the first record but if it were "f4567" it doesn't because "f4567" is the 10th record. Any ideas on how to make this work?

 

[CC214700]

can't you just search to see if vehicles are in service instead of specifying a vegicle number. If you specifying vehicle "e1016" then the code will stop searching as soon as it gets that vehicle number. Or have I miss-understood your problem?

 

[mcadle]

I think a little of both. The table is setup as such:

Vehicle Number : Vehicle Info : In service
text text yes/no checkbox

My query returns all three. I thought that I would have to check in service against vehicle number to get the right one, because here is the data:

e1016 4 door sedan true
f4567 truck false

So, I want the query to search the table and then on the form have a command button with the caption changing dependent upon the true false statement. Does this make sense?

 

[mcadle]

Adendum:

When I had the original code searching for f4567 all it would do is make the button red. It never would go blue.

 

[CC214700]

try cutting this from the code "[Vehicle Number] = "e1016" And " to in effect you will have
"<code>
If [In Service] = True Then
Command2.ForeColor = vbBlue
Else
Command2.ForeColor = vbRed
End If
</code>
"

and if this code can go into the 'form activate' section instead of the 'form open' (if this is posible) that may help

 

[mcadle]

That works for the on activate part. Thanks that makes the changes instant, however, now I have several buttons. When you change in service to false or true it changes all the buttons color.

Private Sub Form_Activate()

If [In Service] = True Then
Command2.ForeColor = vbBlue
Else
Command2.ForeColor = vbRed
End If

If [In Service] = True Then
Command22.ForeColor = vbBlue
Else
Command22.ForeColor = vbRed
End If
End Sub

Command2 is supposed to represent e1016's in service status and command22 is supposed to represent f4567's status. What happens is command22 also represents e1016's status. This is the same problem I have been getting. I think we are going somewhere, where I don't know but we are progressing. Thanks for your fast response and input.

 

[CC214700]

try putting this instead. Hopfully it will work but we may also be back to square 1.

Private Sub Form_Activate()

If [Vehicle Number] = "e1016" And [In Service] = True Then
Command2.ForeColor = vbBlue
Else
Command2.ForeColor = vbRed
End If

If [Vehicle Number] = "f4567" And [In Service] = True Then
Command22.ForeColor = vbBlue
Else
Command22.ForeColor = vbRed
End If
End Sub

 

[mcadle]

Still no go arghhh. I have no idea why this is not working. It just stops at the first record. Even with the new code command2 works fine. But command22 doesn't. I don't know.

 

[CC214700]

Lets have another try then. Try this although it may need slight juggling about just got it spot on.

IF [In Service] = True Then
If [Vehicle Number] = "e1016" Then
Command2.Fore Color = VbBlue
Else
Command2.Fore Color = VbRed
If [Vehicle Number] = "f4567" then
Command22.Fore Clolr = VbBlue
Else
Command11.Fore Color = VbRed
End If
End If
End If

Lets see if we get any further with this.

 

[mcadle]

Damn I thought you had it on that one. It still is stuck on command22. I am trying something else here in a second and will post it.

 

[CC214700]

What about having it like this?

IF [In Service] = True Then
If [Vehicle Number] = "e1016" Then
Command2.Fore Color = VbBlue
Else
Command2.Fore Color = VbRed
End IF
If [Vehicle Number] = "f4567" then
Command22.Fore Clolr = VbBlue
Else
Command11.Fore Color = VbRed
End If
End If

 

[mcadle]

Well, that made it worse. Nothing works with that version, good shot though. I have been trying to do a for loop to search I added a field called id - autonumber so code is like this

for x = 0 to 100
select distinctrow [vehicle number], [in service] from fleet where [id] = x
If [In Service] = True And [Vehicle Number] = "e1016" Then
Command2.ForeColor = vbBlue
Else
Command2.ForeColor = vbRed
End If
next x

I would repeat the code for each vehicle. But this causes an unknown field error. ?????

 

[mcadle]

True True...darn now I am having to think. You are definetly right, however, you can not apply conditional formatting to a command button. So...I am now doing this via a continous form. It is working to an extent. I am figuring out how to have several forms displayed at the same time side by side. Any ideas anyone? CC214700 thanks for all the help you have definetly gotten me further. I appreciate everybody's input it has gotten me further.

 

[mcadle]

Yeah

OK I finally got it working. Here is the problem. Microsoft (in all their infinite wisdom) made a glitch in the program. Even though the query sees all records you can only work with one at a time. No matter how you make the code. We have done for do's, if's, and pretty much any other statement you could come up with and yes even cases. So to do this you have to:

Create a query for every single vehicle in the fleet.
Create a subform for all but one of the vehicles.
Create a form with the one vehicle that you did not do a subform for and put its command button on the main form.
Cut and Paste (a lot of times) the subforms with their properties set so there isn't a border or any buttons.
Put the code that CC214700 so diligently worked with me in the main forms on activate specifying the subform and its respective command buttons, vehicle number, and in service statement.
Then sit back and watch it work.

Thanks a bunch to Pat and CC214700 for getting me going in the right direction. This may not be the most efficient way but it works and is quick and seemless for the end user. I spoke with Microsoft about fixing this or making an additional property were this would work easier and they told me to do the work and they would add it in an additional release. Oh and by the way I wouldn't get paid for it. Thanks a bunch MSOFT.

Once again I can't say thanks enough to the guys that helped me get this issue solved.

 

[mcadle]

Maybe you didn't understand what I was trying to accomplish. I understand what you are saying, however I tried to do that and access only allows one record to be displayed at a time. If you know of a way to display all records in a table of record id = vehicle number on the same form then please tell me. So, basically I want this:

Form single view record source is a fleet query selecting vehicle number and in status from fleet table.

What is is and its source What is displayed on form

text box1 control source vehicle number E1016
text box2 control source vehicle number K3456
etc.

This would be repeated 48 times. I was unable to have 48 text boxes that displayed each record of the table for the same field. Maybe I did something wrong, I don't know. You tell me.

 

[Brianwarnock]

Guess I also don't understand. Taking on board Pat's suggestion I went into one of my datadases, used the wizard to create a tabular form of my table just pulling in the ID and a checkbox field. Added a text box to the detail line, made the conditinal format red if the value in the check field = True, its on click event opened the main form at the record of the ID selected. It allworks . All 47 records in test database show, text boxes are coloured as required main form opens etc etc time taken 10 mins I'm a slow typist.

I always read Pat's responses its better than a tutorial

Brian

 

[mcadle]

OHHHHH I see what you are saying. (sheepish grin) Thanks for the info. I am having to look to see if it will work like I want. I think it will though. It should load a lot quicker to. Thanks for dumbing it down for me.

 

[Brianwarnock]

I'm at home now and won't have access to Access till Monday, but if I've helped I'm happy . Thanks for your response, its surprising how many posters don't at this stage.

Brian

 

[mcadle]

Ah HA I was astounded again. Unfortunately all of the records are listed in a single column. Is it possible to break them up into groups so all 47 records are on one page and the end user doesn't have to scroll down? If you repeat the field then you just get a duplicate of the same records. I tried different queries, no worky. Also, since the form is in continous mode you can't do a subform either. Any ideas?

 

[mcadle]

Ok I took a single form and made subforms that were continous and modified the queries as necessary. Alot less work, however, I still need on click to open a filtered form by that record. Meaning that say the third record is e4567 on click docmd.openform "form name" , , , "[the actual record such as e4567]" but if it was the fifth record it could be e5678. Do you know what I mean? Anyway something to think on.