Automatic forecolor changing & totaling values 1

[jnuus]

Hello,

I have built a rather large relational database for my job in which I enter data concerning my subordinates. Each record brings up a specific person's information. On the main form I use for this, I have a command button which brings up another form in which to enter data into a subtable specific for that individual. However, not everyone will require data on the subform. The problem I want to solve is this: From the main form, the only way to know if there is data on the subform is to click it and look. What I would like is for the text on the command button to be green if there is no data on the subform, and red if there is. That way, I will know by looking at the main form if data exists without having to actually open it up each time. I have some VBA experience and have gotten only partial success with this, but nothing that will change as the subform is populated. Your help with this will be greatly appreciated. Another problem I need help with is this - the tables on the subform for each person requires a value in a field. How do I get Access to add up all of the values and display the total in a label on the form? So far, It will only display the value for only the record displayed. Again, your help with this will be GREATLY appreciated. Thanks!

 

[dzepeda]

First create a Query for the total of each subordinated(based on the table wich contens the subform records):

- Add a unique field for each employee (i.e. Social Security Number or EmployeeID), set the Criteria to FilterEmployee()

- Add the value field :

EmployeeTotal: Sum([FieldForCalculateTotal])

- Save the Query as TekQuery

- Create a new module and put the code

Public Function FilterEmployee() As Integer
    FilterEmployee = Forms![Employee]![ID]
End Function

On the Form create a new Text Box named MyFieldInThisFormForTheTotal

Now in on current event procedure write:

Dim FrmTotal as Integer 'Or the type of the Total
DoCmd.Requery "TekQuery"
FrmTotal=Dlookup("[EmployeeTotal],"TekQuery")
Me.MyFieldInThisFormForTheTotal=FrmTotal
if FrmTotal<>0 then
    Me.MyButton.ForeColor=16384
Else
    Me.MyButton.ForeColor=255
Endif

I hope this solve your problem, plese let me know

 

[jnuus]

Thank you for your help, but I am still having trouble with the forecolor on the command button though. I think it may be because I was probably too generic with my original question. I can't determine from the code you wrote how to apply it to the specific objects I have in my app.

The main form with the command button in question is

frmMain

. The command button to bring up the subform is

cmdShowCitations

. The table is

tblMain

and the table for the subform is

tblTrafficCitation

. The unique field keys are

MemberID

and

CitationID

respectively. There are other fields such as

LastName, FirstName,

etc.

From the main form, I would like

cmdShowCitations

forecolor to be green if the subform does not contain data and red if it does.

Thanks again for helping me out with this!! Also, I would be happy to send it to you if that would help more.

Thanks again.

 

[dzepeda]

Hi

You forgot to say what field link two tables. I suppose MemberID in the tblMain with [red]Member[/red] in tblTrafficCitation, otherwise change the red field in the code.

Forget all the previous code I gave to you and in On Current event procedure of frmMain write:

Private Sub Form_Current()

  Dim AuxVar as Variant
  AuxVar=DlookUp(("[CitationID],"tblTrafficCitation","

[red]

Member

[/Red]

 = " & MemberID)
  If IsNull(Auxvar) Then
    Me.cmdShowCitations.ForeColor=32768

[green]'Green Color if no data[/green]

  Else
    Me.cmdShowCitations.ForeColor=255

[green]'Red Color if there are data[/green]

  Endif

End Sub

Be sure the On Current event of frmMain is set to [Event Procedure]

This must do what you want.

Good Luck

 

[jnuus]

Thanks again for helping me out! We are definitely getting close, but I seem to be having trouble with the

Dlookup

function.

As far as my tables go, the

MemberID

field in

tblMain

is connected with the

MemberID

in

tblTrafficCitation

. That was the only field I could join a relationship with and still maintain Referential Integrity.

The line of code that is giving me trouble is the

Dlookup

function. I have tried:

AuxVar = DLookup("[CitationID]", "tblTrafficCitation", "MemberID = " & MemberID)

and

AuxVar = DLookup("[CitationID]", "tblTrafficCitation", "CitationNumber = " & MemberID)

with no success.

(I used

CitationNumber

because that field will be empty unless a record is present)

With these lines (and many others I tried), the command button stays green and if I run through the records I receive Run Time Error 3705, Syntax Error (missing operator) in query expression 'MemberID = ' after the last one.

I have played with this code for hours and cannot seem to get it to work. The command button either stays all green or all red, depending on how I altered the code.

Again, I REALLY appreciate your help on this. I know the solution is probably staring me in the face, but it is almost 3 am and it's driving me nuts. Any more help will be greatly appreciated! Thanx.

 

[dzepeda]

Two explanations can be the cause of the problem:

1) The field we are looking for is text type. If that's the case modify the sentence as:

AuxVar = DLookup("[CitationID]", "tblTrafficCitation", "[MemberID] = '" & MemberID & "'")

2) There are not relation between the both fields MemberID. In that case find the field who link the tables.
When you open the frmTrafficCitation you probably filter by one field, that's the field we looking for, put in your code and it's ready.

When I see the error code seems to be the field MemberID in your tblMain is Empty. If with the previous you continue receiving the same error, try putting before the dlookup sentence the line

Stop

and when you open the form this open the code window, put the mouse over MemberID word and this show you the value, then if you still can't solve the problem send me a message with this value. Also say how do you open the frmTrafficCitation (the code or query you use)


Greetings from Chile

 

[jnuus]

OUTSTANDING! Thank you so much - the code works perfectly! Not only does my app work, but I have learned from your code as well. Down the road when VB and VBA become a second languages for me, I hope I can help someone like you have helped me. Thanks again!

 

[dzepeda]

I'm happy to help you and thanks for the star, anyway what was the problem...?

 

[jnuus]

I just had to modify the code as you suggested in 1) above. I didn't even think of using the rem apostrophe in quotations until I saw your code. I have learned a little and remember that for future use. Thanks again.