Connecting a checkbox 3

[stormin38]

Can you connect a checkbox to a command that looks at a particular record and searches for a particular word in a specific location of that record and if the value says "xyz" it returns a check in that box and if "xyz" is not displated it leave the check box blank?
Tracy

 

[randy700]

How about...

If dlookup("TheField", "TheTable", "AnotherField = Whatever") = "xyz" Then
TheCheckbox = True
Else
TheCheckbox = False
End If



Randy

 

[stormin38]

Randy, thanks but, I am a NOVICE user. I am assuming I am putting this logic formula into the control source block in the properties of the check box. Is that right?

Okay, I need the formula to find the "Personnel" form, and look into the "IMS" block on that form and find out if it says "RM1" If it does then check the box, if it says something else or nothing at all, leave the check box blank.

 

[randy700]

Try using the form's OnCurrent event.


Randy

 

[rj51cxa]

Hi Randy,

I tried this method to change a check box [Check47] after entering data into a text box [ServiceNumber]. It is part of more code which runs in the BeforeUpdate Event as follows:

Private Sub Service_Number_BeforeUpdate(Cancel As Integer)

[TxtName] = DLookup("[TxtName]", "[TblCompDetails]", "[TxtServiceNumber]=Forms![FrmTeamData].Form![TxtServiceNumber]")
[TxtInitials] = DLookup("[TxtInitials]", "[TblCompDetails]", "[TxtServiceNumber]=Forms![FrmTeamData].Form![TxtServiceNumber]")
If Left([Service Number], 1) = "W" Then [fWRN] = True
If DLookup("[fWonPeters]", "[TblCompDetails]") = "True" Then
    [Check47] = True
Else
   [Check47] = False
End If
End Sub

The code works as far as the "If Left" statement but does not update the check box. I'm not sure if the code is right and would appreciate some assistance.

One thought, the check box is an unbound check box, with no entry in the Data section. Is this signifcant?

Thanks a lot
John

 

[PHV]

If fWonPeters is defined as Boolean in TblCompDetails, then replace this:
If DLookup("[fWonPeters]", "[TblCompDetails]") = "True" Then
with this:
If DLookup("[fWonPeters]", "[TblCompDetails]") = True Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rj51cxa]

Thanks PSV but I'm not sure about the Boolean bit. I've tried looking it up but I'm still not sure exactly what you mean. Could you please explain for me.

Thanks,
John

 

[randy700]

Boolean means it's a yes/no field.


Randy

 

[rj51cxa]

Thanks Randy,
I tried PHV's suggestion and replaced "True" with True but no joy. Any other suggestions would be most welcome.

Best Regards
John

 

[rj51cxa]

Randy and PHV,

I'm not sure what I did but I think it is now working - partly!

The form is in continuous forms format and the check box changes for everyone. I need to make it specific to the data entered in [Service Number].

Any suggestions as to how this can be done? As I commented earlier, the previous lines of code are [Service Number] specific and work well.

Best Regards
John

 

[PHV]

Any UNBOUND control in the Detail Section of a continuous Form has a SINGLE value standing for ALL the records.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rj51cxa]

Thanks PHV,

I'll take another look and reply tomorrow if necessay.

Best Regards
John

 

[rj51cxa]

Hi PHV,

I've added a Yes/No field to the underlying table and bound the check box to that. Unfortunately, nothing is working at all now, so I'm a little confused. The code now look like this:

If DLookup("[fWonPeters]", "[TblCompDetails]") = True Then
    [fWonPeters] = True
Else
    [fWonPeters] = False
End If

It may help if I describe what I have:

I have two tables - TblCompetitor and TblCompDetails. TblCompetitor stores data for the current year and TblCompdetails stores the name, initials, service number for competitors past and present, together with details of competitions they have won in past years.

The form I am using is bound to TblCompetitor.

In my form I enter the service number which then looks up the name and initials in TblCompDetails and enters them in the appropriate text boxes in the form (see code in earlier post above). I also want the lookup to check in TblCompDetails to see if the competitor, whose service number I have just entered, has won this prize in the past. If the field in TblCompDetails is checked, then the check box on my form should also be checked.

With this in mind, could you please take a look at my code and see if there is anything wrong.

Best Regards
John

 

[PHV]

You should add a 3rd argument to your DLookUp function call, saying which row of TblCompDetails you want to check.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rj51cxa]

PHV,

I see what you are getting at. I think I need the DLookup to refer to "[TblCompDetails]", "[TxtServiceNumber]" but I'm not sure of how this fits into the code.

Best Regards
John

 

[randy700]

If DLookup("[fWonPeters]", "[TblCompDetails]") = True Then
[fWonPeters] = True

Your checkbox field is fWonPeters?


Randy

 

[rj51cxa]

Randy,

My checkbox in my form has the name "Won Peters" and the control source is [fwonPeters]. Which one should I refer to in the code? I have actually tried both but it still does not work.

I think PHV may have hit on the problem - it needs to know which record in TblCompDetails to compare.

Best Regards
John