Comparing to fields in a table to display a cmd button on a form

D

drybone

Registered User.
Local time
Today, 03:37
Joined
Mar 31, 2015
Messages
29
Hello all,
I'm having some difficulty running a Private sub. every time it loads, it gets a run time error "2465" (can't find field 'l1' referred to in your expression.)

Info to help you all get what I'm trying to explain:

Table name = TblUsers
It has 26 fields:
EDIPI = unique ten digit number (primary key assigned)
AdditionalDutiesRosterAdmin = Yes/no
Training = Yes/No
Supply = Yes/No

Those are a few of the fields I'll list for sample data. The tables yes/no fields are what I want to use to give people access to different forms in the database.

Next, I have a form that has a list of buttons that correlate to the yes/no fields in the above mentioned table. On that forms "Private Sub Form_Load()" I'm trying to have it look at the table "TblUsers" EDIPI field and compare it to current user (in code below is Me.Text59). Text59 pulls user name straight from computer with no problems. The problem happens with the second half of the If statement. I want it to look at the record that specifically has the EDIPI and see if the "AdditionalDutiesRosterAdmin" is yes. If it is Yes then the button "CmdAddRoster" will be displayed or if not then it won't display.

Below is the code that I've tried



Code: 
 Private Sub Form_Load()
If DCount("*", "[TblUsers]", "[EDIPI] = '" & Me.Text59 & "'") > 0 And ([tblusers]![AdditionalDutiesRosterAdmin] = Yes) Then
   CmdAddRoster.Visible = True
   
Else
    CmdAddRoster.Visible = False
End If
 End Sub
 
One thing I notice is you have the ID field set up like a string, but you stated it's numeric. You would need to remove the single quotes from your DCount:
Code: 
DCount("*", "[TblUsers]", "[EDIPI] = [COLOR="Red"]'[/COLOR]" & Me.Text59 [COLOR="red"]& "'"[/COLOR]) > 0 And ([tblusers]![AdditionalDutiesRosterAdmin] = Yes)

Also, you could simplify your approach by opening a recordset, then setting the visibility of the buttons to the appropriate fields:
Code: 
Private Sub Form_Load()
    Dim rs As Recordset

    Set rs = CurrentDb.OpenRecordset("SELECT * FROM TblUsers WHERE EDIPI = " & Me.Text59)

    Me.CmdAddRoster.Visible = rs!AdditionalDutiesRosterAdmin
    Me.CmdTraining.Visible = rs!Training
    Me.CmdSupply.Visible = rs!Supply
    'Additional fields
End Sub
 
Code: 
If DCount("*", "[TblUsers]", "[EDIPI] = '" & Me.Text59 & "'") > 0 And ([tblusers]![AdditionalDutiesRosterAdmin] = Yes) Then
Click to expand...

Where is it supposed to be getting the value for [tblusers]![AdditionalDutiesRosterAdmin]? You haven't given it a way to retrieve that data. You might try a DLookup, although that can be slow if the table is large. If performance is adequate on the DCount is should be fine for the DLookup.
 
MarlaC said:
Where is it supposed to be getting the value for [tblusers]![AdditionalDutiesRosterAdmin]? You haven't given it a way to retrieve that data. You might try a DLookup, although that can be slow if the table is large. If performance is adequate on the DCount is should be fine for the DLookup.
Click to expand...

^^^This is my issue and I'm not sure on how to go about writing it. I want the code to lookup a record utilizing the EDIPI then search all the yes/no fields in that record. Any yes/no field in that record that is yes I want displayed and the ones that are no don't display.

TJPoorman said:
One thing I notice is you have the ID field set up like a string, but you stated it's numeric. You would need to remove the single quotes from your DCount:
Click to expand...

I need to correct my original statement that it is numerical. it is actually 11 characters long and is alphanumerical in nature:

1234567890A is the format.

Sorry for the miscommunication, the second half of your post looked like the direction I would need to go in but it threw errors when I loaded the page.

Runtime Error 3075
syntax error (missing operator) in query expression 'EDIPI = 1234567890A'

^That happens when I run the code you provided earlier...

Code: 
 Private Sub Form_Load()
    
    WaitSeconds (3)
    Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM TblUsers WHERE EDIPI = " & Me.Text59)
     Me.CmdAddRoster.Visible = rs!AdditionalDutiesRosterAdmin
    Me.CmdTraining.Visible = rs!Training
    Me.CmdSupply.Visible = rs!Supply
    'Additional fields
End Sub
 
I think, then, that
Code: 
Private Sub Form_Load()
If DCount("*", "[TblUsers]", "[EDIPI] = '" & Me.Text59 & "'") > 0 And ([tblusers]![AdditionalDutiesRosterAdmin] = Yes) Then
   CmdAddRoster.Visible = True
   
Else
    CmdAddRoster.Visible = False
End If
 End Sub
should be
Code: 
Private Sub Form_Load()
If DCount("*", "[TblUsers]", "[EDIPI] = '" & Me.Text59 & "'") > 0 And (DLookup("[AdditionalDutiesRosterAdmin]","tblusers","[EDIPI] = '" & Me.Text59 & "'") = True) Then
   CmdAddRoster.Visible = True
   
Else
    CmdAddRoster.Visible = False
End If
 End Sub

You may also want to add the same code (once tested working) to your form's current event so that it hides and displays the button if the record is changed without the form being reloaded. Also, it's a helpful practice to give controls meaningful names so that you know what's supposed to be in Text58 and Text59 and so one when in code.
 
Drybone,

You say
EDIPI = unique ten digit number (primary key assigned)

If the field is numeric, you should not have quotes around the criteria string
viz
DCount("*", "[TblUsers]", "[EDIPI] = " & Me.Text59 ) > 0
 
Since this is AlphaNumeric you will need to use single quotes. Like this:
Code: 
Set rs = CurrentDb.OpenRecordset("SELECT * FROM TblUsers WHERE EDIPI = '" & Me.Text59 & "'")
 
Thank you all for your help! both ways worked but it seemed like TJPoorman's method requires a lot less typing. Another question for you guys. If I wanted to collapse the space for the omitted buttons to keep everything racked and stacked nice and tight would it be possible?

It is a form and I've done it successfully with reports but I don't seem to be given the same property options.
 
This is what I've tried thus far. It does set the height to zero like it should but the can grow and can shrink settings to yes don't collapse the remaining displayed buttons.

Code: 
 Private Sub Form_Load()
    
    Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM TblUsers WHERE EDIPI = '" & Me.Text59 & "'")
     If (Me.CmdAddRoster.Visible = rs!FldAddRoster) Then
        CmdAddRoster.Visible = True
     Else
        CmdAddRoster.Visible = False
        CmdAddRoster.Height = 0
     End If
     
     Me.CmdTraining.Visible = rs!FldTraining
     Me.CmdSupply.Visible = rs!FldSupply
     'Additional fields
End Sub

I only did the first one to test it out and the other 2 I didn't build the If and Else.
 
Can Grow/Shrink doesn't apply to the form when it is on screen, only during print.

This code will do what you're looking for. You will need to make 3 changes.
1. Change the values indicated in the code to match your situation
2. Change the "Tag" property of the controls you want to stack to "StackEm"
3. Verify the Tab Order of the buttons is correct (The code loops by Tab Order)

Code: 
Private Sub Form_Load()
Dim ctl As Control
Dim lngStart As Long
Const intButtonHeight As Integer = 360 'Set this to the height of your controls.  Value is in Twips
Const intButtonSpace As Integer = 180 'Set this to the gap between your controls.  Value is in Twips
Dim rs As Recordset

lngStart = 1440 'Set this to the start point of your top control.  Value is in Twips

'Code to hide/show buttons
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM TblUsers WHERE EDIPI = '" & Me.Text59 & "'")
    Me.CmdAddRoster.Visible = rs!FldAddRoster
    Me.CmdTraining.Visible = rs!FldTraining
    Me.CmdSupply.Visible = rs!FldSupply
    'Additional fields

For Each ctl In Me.Controls
    If ctl.Tag = "StackEm" And ctl.Visible Then
        ctl.Top = lngStart
        lngStart = lngStart + intButtonHeight + intButtonSpace
    End If
Next
End Sub
 
TJPoorman said:
Can Grow/Shrink doesn't apply to the form when it is on screen, only during print.

This code will do what you're looking for. You will need to make 3 changes.
1. Change the values indicated in the code to match your situation
2. Change the "Tag" property of the controls you want to stack to "StackEm"
3. Verify the Tab Order of the buttons is correct (The code loops by Tab Order)

Code: 
Private Sub Form_Load()
Dim ctl As Control
Dim lngStart As Long
Const intButtonHeight As Integer = 360 'Set this to the height of your controls.  Value is in Twips
Const intButtonSpace As Integer = 180 'Set this to the gap between your controls.  Value is in Twips
Dim rs As Recordset

lngStart = 1440 'Set this to the start point of your top control.  Value is in Twips

'Code to hide/show buttons
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM TblUsers WHERE EDIPI = '" & Me.Text59 & "'")
    Me.CmdAddRoster.Visible = rs!FldAddRoster
    Me.CmdTraining.Visible = rs!FldTraining
    Me.CmdSupply.Visible = rs!FldSupply
    'Additional fields

For Each ctl In Me.Controls
    If ctl.Tag = "StackEm" And ctl.Visible Then
        ctl.Top = lngStart
        lngStart = lngStart + intButtonHeight + intButtonSpace
    End If
Next
End Sub
Click to expand...

This was exactly what I was going for! You sir are a genius, thank you so much for helping me through this problem and teaching me something new!
 
You must log in or register to reply here.

Similar threads

J
Match the primary Key in the Main form to the foreign key in the Subform
Replies
9
Views
343
MajP
MajP
J
VBA to print Word file and close is
Replies
8
Views
699
DickyP
DickyP
N
Solved Form Record Cloning In MS Access
Replies
8
Views
505
Ken Sheridan
Ken Sheridan
N
Solved How to scan a barcode from a parent form and update the child/subform in MS access
Replies
3
Views
657
Mark_
M
jwcolby54
Building a table for my book
Replies
15
Views
1,256
jwcolby54
jwcolby54

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom