Results 1 to 12 of 12
  1. #1
    Join Date
    May 2006
    Posts
    386

    Unanswered: Need Help with If and Where Clause to Show/Hide fields

    When it comes to codes, I am a novice, so please come easy on me. Basically, I have added three new fields in my database (booking page) which relates to one particular client account, which means I do not want those three fields to be visible or required if any other client is searched in the database.

    I know the concept but not good with code. So, it should be something like this:

    IF cliend ID = to 72 THEN Show those three fields and make them Required Fields

    Else

    Hide

    I would really appreciate any help on this particularily how the code should look like.
    Emi-UK
    Love begets Love, Help Begets Help

  2. #2
    Join Date
    May 2012
    Posts
    89
    Hi Emal.

    Maybe this is what you need;

    If Me.cliend ID > 72 then
    Me.Field1.visible = true
    Me.Field2.visible = true
    Me.Field3.visible = true
    Else
    Me.Field1.visible = False
    Me.Field2.visible = false
    Me.Field3.visible = False
    End if

    Set the 3 fields visible to NO
    If the falue of cliend ID needs to be above 72 use the > parameter below 72 <
    Also i would not recoment the Required Fields option on these 3 field as is there not visible then access will need an value in then and so create an error

    To ensure that al the data is entered you can use the code below to ensure that the save record button is true/false after update


    '
    ' Class Module of the Form
    '
    Option Compare Database
    Option Explicit

    Private m_colTextBox As Collection

    Private Sub Form_Current()

    CheckCompleted

    End Sub

    Private Sub Form_Open(Cancel As Integer)

    Dim ctl As Control

    Set m_colTextBox = New Collection
    For Each ctl In Me.Controls
    If ctl.ControlType = acTextBox Then
    m_colTextBox.Add ctl
    ctl.AfterUpdate = "=CheckCompleted()"
    End If
    Next ctl

    End Sub

    Private Function CheckCompleted()

    Dim ctl As Control
    Dim booEnabled As Boolean

    booEnabled = True
    For Each ctl In m_colTextBox
    If Nz(ctl.Value, "") = "" Then
    booEnabled = False
    Exit For
    End If
    Next ctl
    Me.ConfirmButton.Enabled = booEnabled

    End Function
    Last edited by Rubberducksucker; 08-12-12 at 03:46.

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I have no idea where RubberDuckSucker came up with the idea that this should be based whether or not the client ID was more than 72, since it obviously has to be based on whether or not it is equal to 72. Also have no idea where the concept of having a 'save' button came from, either, since it wasn't mentioned by the OP, and, in point of fact, goes against the way Access natively saves a Record, by Default, and generally, from the threads I see here on the subject, causes more problems than it solves! By Default, Access Saves a Record when the user moves to another Record or closes the Form/Database. No other intervention by the developer is needed.

    Normally you'd start this sort of thing by using the AfterUpdate event of the Client ID Control to set the Visibility of the Controls in question, when the cliend ID is first entered, and if this is needed, this first bit of code can be used in that AfterUpdate event, as well.

    But from your wording, it sounds as if you only want this function to perform when reaching a given Record thru a search, so we'll only deal with the Form_Current and Form_BeforeUpdate events:

    Code:
    Private Sub Form_Current()
    
     If Me.[cliend ID] = 72 Then
      Me.Field1.Visible = True
      Me.Field2.Visible = True
      Me.Field3.Visible = True
     Else
      Me.Field1.Visible = False
      Me.Field2.Visible = False
      Me.Field3.visible = False
     End if
    
    End Sub


    Rubberducksucker is correct in saying that you cannot simply set the three Fields as Required, as this would error-out on all Records where the ID wasn't 72. So to assure that these Fields are populated when the ID is 72, we'll use Validation in the Form_BeforeUpdate event:

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    
     If Me.[cliend ID] = 72 Then
      
      If Nz(Me.Field1,"") = "" Then
        Msgbox "Field1 Must be Filled In!"
        Cancel = True
        Field1.SetFocus
        Exit Sub
      End IF
    
      If Nz(Me.Field2,"") = "" Then
        Msgbox "Field2 Must be Filled In!"
        Cancel = True
        Field2.SetFocus
        Exit Sub
      End IF
    
      If Nz(Me.Field3,"") = "" Then
        Msgbox "Field3 Must be Filled In!"
        Cancel = True
        Field3.SetFocus
        Exit Sub
      End IF
    
    End If
    
    End Sub


    Some important things to note:

    First off, you give your Control name as cliend ID, with 'client' being misspelled, assuming that this is in English. I've left it spelled as cliend, so if that was a typo, you'll need to correct it everywhere it appears.

    Secondly, because you have a Space between cliend and ID, Access VBA requires that you enclose it in Square Brackets. This tells the Access Gnomes that it is, in fact, an object name.

    Thirdly, the line

    If Me.[cliend ID] = 72 Then

    is only valid if [cliend ID] is defined as a Number Datatype.

    If [cliend ID] is defined as a Text Datatype, the line needs to be

    If Me.[cliend ID] = "72" Then

    Finally, Field1, Field2 and Field3 needs to be replaced, in all code, with the actual names of the Controls in question.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    May 2006
    Posts
    386
    First of all, thank you for your resopnses. I am grateful.
    Misslinglinq is right in assuming that ClientID which by the way in my table is as ID should be equal to 72 and not greater or smaller. To shed a bit more light on this, I have one main form called MainBookingForm within this I have a search option, and within this MainBookingForm I have a SubForm called Booking. So, I search for a client ID then the SubForm brings up all records against that Client ID (ID).

    Now, I tried your code and I changed ClientID to (ID) as below), and I used the same code, once on AfterUpdate event on the MainBookingForm, it did not work, AND Then I used the same code on AfterUpdate of the Booking form which is the subform and it still did not work. Basically, the three fields are always invesible.

    Private Sub Form_Current()

    If Me.[Client.id] = 72 Then
    Me.BudgetHolderName.Visible = True
    Me.BudgetHolderContact.Visible = True
    Me.BudgetHolderEmail.Visible = True
    Else
    Me.BudgetHolderName.Visible = False
    Me.BudgetHolderContact.Visible = False
    Me.BudgetHolderEmail.Visible = False
    End If

    End Sub
    I then used it in MaingBookingForm' s current event and now I am getting this message Method or data member not found


    Any help would be highly appreciated.
    Last edited by Emal; 08-15-12 at 15:26.
    Emi-UK
    Love begets Love, Help Begets Help

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    What is the exact name of the Textbox that holds your client ID field? You originally say


    If cliend ID = to 72

    Assuming that the name actually was cliend ID, i gave you

    If Me.[cliend ID] = 72

    with the name enclosed in Square Brackets because of the Space in the name.

    You've now changed it to

    If Me.[Client.id] = 72 Then

    taking the Space out if the name and replacing it with a dot! It has to be the exact name of the Textbox! It cannot be 'almost' the same, it has to be 'exactly' the same!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    May 2006
    Posts
    386
    OK, these are the exact names, and basically in the underline Client table, the actual client id is ID and not Client ID or ClientID. So First of all, please let me know whether I should insert this code OnCurrent event of the MainBookingForm or Booking form which has details of all bookings against a client, which is also the subform within MainBookingForm. Secondly, what am I doing wrong with this
    code as it is giving me the same compile error.
    Private Sub Form_Current()

    If Me.id = 72 Then
    Me.BudgetHolderName.Visible = True
    Me.BudgetHolderContact.Visible = True
    Me.BudgetHolderEmail.Visible = True
    Else
    Me.BudgetHolderName.Visible = False
    Me.BudgetHolderContact.Visible = False
    Me.BudgetHolderEmail.Visible = False
    End If

    End Sub
    Emi-UK
    Love begets Love, Help Begets Help

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    after some 360 posts over 6 years you should be able to describe this problem in a more useful manner. it also looks like in those intervening 6 years you haven't honed your debugging skills. do a google on MS access debugging. it will help you resolve these sort of problems quickly

    which line reports the error
    is it a compiler error or a runtime error

    the code should be placed in the relevant events

    which should be
    the forms on current event
    the control that contains the client ID... probably the controls on lost focus event, but also may include on change

    if it were me I'd simplify your code

    Code:
    Dim IsControlVisible as boolean 'temp var to hold state
    
    If Me.id = 72 Then 'set flag if the customer ID is...
      IsControlVisible = true 'then show controls
    else
      IsControlVisible = false 'then dont' show controls
    endif
    Me.BudgetHolderName.Visible = IsControlVisible 
    Me.BudgetHolderContact.Visible = IsControlVisible 
    Me.BudgetHolderEmail.Visible = IsControlVisible
    as to which form you place this code then it should be the form that has the controls in
    OR if the customer ID is on a different from then you need to specify which forms have what values.

    me.blah refers to the current form.
    forms!thatform.balh refers to a form called thatform
    but you need to ensure you have appropriate error trapping in place.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by Emal View Post
    OK, these are the exact names, and basically in the underline Client table, the actual client id is ID and not Client ID or ClientID.
    This still sounds as if you're using the Field names from the Client Table (not really sure how something can be 'basically' in a Table!) Is ID also the name of the Textbox Control, on the Form, that the ID is being displayed in?

    To repeat, regardless of the code you use, the

    Me.ID

    in

    If Me.id = 72 Then

    has to be the name of a Control on your Form! IS this the situation?

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  9. #9
    Join Date
    May 2006
    Posts
    386
    First of all, I would like to thank Healdem and Missinglinq for your directions. Healdem, I understand what you are saying about my previous issues regarding debugging, but I have never claimed that I am an excellent especially when codes get a little complicated. However, Thank you for your suggestions which worked fine for me. However, I am not having Tab order issues and also when these fields are visible they clash with three other fields which are always available regardless of clients ID. Am not sure how to address this.
    Emi-UK
    Love begets Love, Help Begets Help

  10. #10
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Quote Originally Posted by Emal View Post
    having Tab order issues
    What kind of issues? Do they go in the wrong order, cause errors, make you mad, blow up the computer, cause world peace?

    Quote Originally Posted by Emal View Post
    also when these fields are visible they clash with three other fields which are always available regardless of clients ID. Am not sure how to address this.
    What do you mean by clash? If the three other fields are invalid when the three custom fields are shown, then just hide/show them like the custom fields.

    Steve

  11. #11
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by Emal View Post

    ...I am not having Tab order issues and also when these fields are visible they clash with three other fields which are always available regardless of clients ID.

    ...Am not sure how to address this.
    Perhaps you meant to say "I am now having Tab order issues?" If so, as Steve asked, how so?

    Ditto the "they clash with three other fields;" We cannot help you with problems that we cannot see!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  12. #12
    Join Date
    May 2006
    Posts
    386
    Hi All, Thank you for your suppport. It is all fine now, basically, I meant to say the tab order was not working when the 3 fields are visible, but I kind of worked around it and it is works fine. Basically I repositioned fields and it is fine now. Thank you every so much!
    Emi-UK
    Love begets Love, Help Begets Help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •