Change Button Properties Based On Records In Query

[jwleonard]

I have a database to track parts through a repair cycle. I need to show if a part has been in the shop before based on it's serial number. I have a button that opens a form based on a query that shows records that match the current record serial and part numbers (both have to match). I would like to change the button fore color to red if there are any records produced by that query. The goal here is to have the button on the main form all the time, then if the part has been in the shop before, the button text turns red (or something to draw attention). Then the user would know the part has been repaired here before and they should click the button the see the history.

 

[WayneRyan]

jw,

Need more info, but ...

You can use the form's OnCurrent event to do something like this:

If DCount("[PartNumber", "YourTable", "[PartNumber] = " & Me.PartNumber) > 0 Then
   Me.YourButton.ForeColor = 255
Else
   Me.YourButton.ForeColor = 0
End If

hth,
Wayne

 

[jwleonard]

Wayne,
I follow you but can DCount be used against a query? Your example shows a field in a table, I need it to go against a query. Plus the query isn't a database object, it is the record source for the form.

The just of what I am attmpting to do is have the button show if there will be any results from the query before it is clicked.

What other information are you needing?
Thanks, I will see what I can do with DCount in the meantime.

 

[WayneRyan]

jw,

You display a record on your form.
A query feeds your form.
The form displays a PartNumber.

The OnCurrent event of the form will use the PartNumber displayed in
a DCount that looks at your table. Depending on the number found
in your table, change the command button red/black.

Wayne

 

[jwleonard]

The form the button is on is not the form with the query.
ie.. On form A there is button 1, button 1 opens form B. I want button 1 text to change color if form B will show any records.

If I can get a query in DCount I think it will work, at least I am heading somewhere now. I can't believe I didn't think of DCount right of the bat. Thanks

 

[jwleonard]

Good News, I was able to make it work! Its funny, I searched all over the place for a solution and couldn't find anything, then as soon as you said DCount I was able to find all kinds of examples. It was basically the same as checking for duplicate records. Thanks for the help Wayne! Here is what I used for future reference:
Private Sub Form_Current()
If IsNull(Me.WorkorderID) Then
Me.cmdShowHistory.ForeColor = 0
Me.cmdShowHistory.Caption = "No History"
Else
If DCount("[WorkorderID]", "[TBLWorkorders]", "[MainPartID] = " & Me.MainPartID & " And [SerialNumber] = '" & Me.SerialNumber & "' And [WorkorderID] <> " & Me.WorkorderID & "") > 0 Then
Me.cmdShowHistory.ForeColor = 255
Me.cmdShowHistory.Caption = "Part History"
Else
Me.cmdShowHistory.ForeColor = 0
Me.cmdShowHistory.Caption = "No History"
End If
End If
End Sub

 

[WayneRyan]

JW,

Glad to hear that you got it working.

btw, for future code posts:

{Code}
Your code here ...
{/Code}

BUT, change the braces to brackets: {} --> []
then your code will preserve its spacing and be highlighted.

Hope to hear from you again.

Wayne