Results 1 to 15 of 15
  1. #1
    Join Date
    Sep 2003
    Posts
    35

    Unanswered: Novice Questions - Forms

    So I'm building a form that is going to be predominantly used for adding new records, but also for minimal record updating. It has all the fields in one of my tables. Each record will be autonumbered with a recordID (and therefore not necessary for the user to enter in the form). However, I'd like to display (and grey out) the ID on the top right side of the form just for accounting purposes and make this field un-editable. Is this possible?

    Also, I want to add a radio button/or checkbox of some sort to allow for 'more information' to be added to each record, if possible. On the backend, this will be thrown into an 'additional_info' table with 2 columns. If the user checks the checkbox, I'd like these two fields to pop up in the form (or un-grey -- essentially activate). How can this be done?

    Thanks

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    To make a control un-editable, set the Locked or the Enabled property to False. This can be set in either the control's Property Sheet or in VBA.

    Ergo, you can set the property sheet for the autonumber field in the Property Sheet for that field, and it will be permanently locked (prevents data from being entered) or disabled (greyed out).

    For the other fields you want activated only when needed, I suggest you make these fields - both their labels and textboxes - invisible. In the form's OnCurrent event, type Me.LabelX.Visible = False. Repeat for the other label and for both textboxes. Make a command button, say called cmdAddData. In cmdAddData OnClick event, type
    Code:
    Me.LabelX.Visible = True
    Me.LabelY.Visible = True
    Me.TextX.Visible = True
    Me.TextY.Visible = True
    Me.TextX.SetFocus
    When the user saves the record, and goes to a new record, the two fields will revert to invisible.

    Sam

  3. #3
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    A slight enhancement to Sam's proposal...

    As the code stands, if you are browsing records you won't see the extra fields unless you click the command button on each record. If you want the extra fields to display when they have an entry then change the OnCurrent event to:

    If IsNull(me.TextX.Value) and IsNull(me.TextY.Value) Then
    Me.LabelX.Visible = False
    Me.LabelY.Visible = False
    Me.TextX.Visible = False
    Me.TextY.Visible = False
    Else
    Me.LabelX.Visible = True
    Me.LabelY.Visible = True
    Me.TextX.Visible = True
    Me.TextY.Visible = True
    End If


    hth
    Chris

  4. #4
    Join Date
    Sep 2003
    Posts
    35
    Thanks to the both of you.

    Two trivial questions (sorry, new at this):

    Am I adding the code in as an Event Procedure (in the VBasic editor)?

    Does it matter that these additional fields are subforms? If so, how do I revise the code - I'm most concerned about this Me.LabelX.Visible syntax.

  5. #5
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Quote Originally Posted by tkchung
    Am I adding the code in as an Event Procedure (in the VBasic editor)?
    Yes.

    Quote Originally Posted by tkchung
    Does it matter that these additional fields are subforms? If so, how do I revise the code - I'm most concerned about this Me.LabelX.Visible syntax.
    Yes it does matter. I pinched this from a website (because I'm too lasy/tired to type it and I never get it right first time ):

    Access treats subforms as controls. So, when referencing them, the proper syntax is

    Forms![formname]![controlname]

    When referencing a control on a subform, you use the Form identifier in the form

    Forms![formname]![controlname].Form![subformcontrolname]



    Add a .value (although this is the default so not necessary) or .visible to the end of each to set the respective attributes. You can obviously set other attributes in a similar manner.

    Sam used me. which is a way of saying use the form/report that currently has the focus although personally I tend to write the full reference through habit.

    Note: a form/subform needs to the open if you are going to reference controls on it.

    Chris

  6. #6
    Join Date
    Sep 2003
    Posts
    35
    Attempting to follow your syntax, this is what I came up with. I get a debug error that says it can't find the form (although the forms are there).

    Private Sub Form_Current()
    If IsNull(Forms!Addl_Procedures_subform!Addl_Procedur e_Date.Value) And IsNull(Forms!Addl_Complications_subform!Addl_Compl ication_Date.Value) Then
    Forms!Addl_Procedures_subform!.Visible = False
    Forms!Addl_Complications_subform!.Visible = False
    Else
    Forms!Addl_Procedures_subform!.Visible = True
    Forms!Addl_Complications_subform!.Visible = True
    End If
    End Sub

  7. #7
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    I'm not sure if I understand what you are trying to do here. You appear to have to subforms and I'm guessing you are trying to make the subforms invisible (?). You don't appear to have referenced your form at all.

    Why do you want to put your extra fields in subforms ? Are they from a different table ?

    Anyway, I'll try and explain referencing again...

    Suppose you have a form called myForm and a subform on the main form called mySubForm. Also, you have a field on the subform called mySubField1. Then the value of the field can be referenced using:

    [Forms]![myForm]![mySubForm].[Form]![mySubField1]

    Try this....
    Create an unbound text box on your main form. Put your reference expression in the control source of the text box with an = sign like this:

    =[Forms]![myForm]![mySubForm].[Form]![mySubField1]

    When you view the form you should see the value in mySubField1 repeated in your new text box. The point is it will prove to you whether your referencing is working or not. Don't bother working on VBA until you get this principle right.

    hth
    Chris

  8. #8
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Quote Originally Posted by tkchung
    Attempting to follow your syntax, this is what I came up with. I get a debug error that says it can't find the form (although the forms are there).

    Private Sub Form_Current()
    If IsNull(Forms!Addl_Procedures_subform!Addl_Procedur e_Date.Value) And IsNull(Forms!Addl_Complications_subform!Addl_Compl ication_Date.Value) Then
    Forms!Addl_Procedures_subform!.Visible = False
    Forms!Addl_Complications_subform!.Visible = False
    Else
    Forms!Addl_Procedures_subform!.Visible = True
    Forms!Addl_Complications_subform!.Visible = True
    End If
    End Sub
    Hi, TK,

    I'm going to leave your statement intact so you can compare it with what perhaps should be:

    If IsNull(Forms!Addl_Procedures_subform!Addl_Procedur e_Date.Value) And IsNull(Forms!Addl_Complications_subform!Addl_Compl ication_Date.Value) Then
    Forms!Addl_Procedures_subform.Visible = False
    Forms!Addl_Complications_subform.Visible = False
    Else
    Forms!Addl_Procedures_subform.Visible = True
    Forms!Addl_Complications_subform.Visible = True
    End If

    What I did? Not much, only removed several bangs (!) where they don't belong. Bangs are used to separate control names, but periods are used to separate control names from their properties (Collection!ObjectName!ControlName.Property). Get it?

    BTW, here on the forum, you can make your code look like real code, if you surround it with code marks. Just select the entire code block and click on the "#" icon above to add the code marks, as follows:
    Code:
    If IsNull(Forms!Addl_Procedures_subform!Addl_Procedure_Date.Value) And IsNull(Forms!Addl_Complications_subform!Addl_Complication_Date.Value) Then
        Forms!Addl_Procedures_subform.Visible = False
        Forms!Addl_Complications_subform.Visible = False
    Else
        Forms!Addl_Procedures_subform.Visible = True
        Forms!Addl_Complications_subform.Visible = True
    End If
    Also, notice on the top line of the code, there is a separation in the word 'complication':
    Addl_Compl ication_Date.Value
    This is probably why Access is telling you the error message that your form does not exist. If you fix this line, (and of course take my wonderful advice) your code will probably work. If you find that your code doesn't have any such separation, they are probably "hidden characters." Delete the entire word and retype it.

    Sam

  9. #9
    Join Date
    Sep 2003
    Posts
    35
    Howey,
    To answer your questions, this is what I'm attempting to do. There is a standard case that is being captured in the main table Cases. Now, occasionally we need to provide follow-up data on some of these cases - this is similar to a help desk application, where a case may have 1 conversation or 10 conversations linked to it. So the schema is such that additional follow-up are all captured in a separate table. From what I understand, you can only reference different tables on the same form through subforms. If there is a more direct way (such as a text box), that would be great - I just don't know how to reference different tables like that.

    Now, there are two different follow-up pieces of information to collect (and therefore two different subforms, a Procedures form and a Complications form). I'd like to add a checkbox for each of these forms if there are additional procedures or if there are additional complications. Only if there needs to be a follow-up, do they need the field to be visible. HTH.

    I've tried the updated code from both of you, but it still cannot find the form. I've also tried to do a simple reference, which is working.

  10. #10
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    I agree, a subform setup is the way to go for what you describe.

    I donít think your references are right yet. What is the name of your main form ??? You donít seem to have mentioned it.

    Note that the subform is treated as a control on the form (and therefore you canít simply reference it as if it was a form in itís own right). You must reference it by its control name (which may be different to the name of the actual subform) like you would if it were a text box. Right-click on the edge of the subform box (the control) and select properties to see the name of the control.

    The syntax is:
    Forms!myMainFormName!myControlNameOf SubForm.attribute

    where attribute is the attribute you are trying to set e.g. visible

    Something to try...
    Create an unbound textbox on your main form and type the following as your control source (replacing myMainFormName and myControlNameOf SubForm as appropriate):
    = Forms!myMainFormName!myControlNameOfSubForm.name

    This should show you the name of the control for the subform.

    Use the text box to test all the references you are using in your code and see the attribute values.

    Another unrelated question...Are your check boxes bound ?

    Can you post your d/b ?

    Chris

  11. #11
    Join Date
    Sep 2003
    Posts
    35
    Chris,
    Thanks - that's pretty helpful (beyond this problem I'm having). As an aside, I seriously need to learn VB (can you suggest any books on learning all of this?) - this isn't like Excel which you can just mess around with formulas.

    My main form is called "Cases", which is bound to the table with the same name. As you correctly guessed, the subform object name (Addl_Procedures) is different from the subform name itself(Addl_Procedures_subform). I ended up with the following syntax for the OnCurrent event for the Cases form:

    Private Sub Form_Current()
    If IsNull(Forms!Cases!Addl_Procedures) Then
    Forms!Cases!Addl_Procedures.Visible = False
    Else
    Forms!Cases!Addl_Procedures.Visible = True
    End If
    End Sub

    However, the subform still is visible even with a new record. I've tried changing the IsNull line, but any attribute of the subform I attempt to add gets an error.

  12. #12
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Forms!Cases!Addl_Procedures is not right. It's like saying "tell me the value of my control (which is a subform). It's a bit meaningless. You need to also reference the control on the subform.

    replace it with:

    Forms!Cases!Addl_Procedures.Form!myControlNameOnMySubForm

    Replace myControlNameOnMySubForm with one of the control names on your subform that you are using to check.

    As for a book, i can really recommend:
    Access Database Design & Programming 3rd Edn, by Steven Roman, O'Reilly Sabastapol,CA (ISBN 0-596-00273-4)
    It's significantly above basic Access to be of great to someone who is no longer a novice but isn't hardcore programming so there's no assumed level of programming.

    Stick with it. Referencing subforms is one of the few things I find logical in Access. You'll have a big smile when you get it to work.

    Chris

  13. #13
    Join Date
    Sep 2003
    Posts
    35
    Alright. I finally got it to work. My understanding of "controls" and "forms" finally clicked - it was just a matter of identifying the right objects to point to.

    Now, I just want to use a checkbox or a radio button to "toggle" visibility. I've gotten it to work to turn on via OnClick. Is there another property that allows you to turn off via OnUnclick or something like this? OnDblClick is unintuitive, but functional.

    I should add that this is an unbound checkbox. It's really just there as a visibility switch. Is there anyway to specify the "value" of the checkbox?
    Last edited by tkchung; 09-26-05 at 18:06.

  14. #14
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Hey well done.

    btw, i mean't illogical in my last post

    A radio button would work but they are really designed for multiple options e.g. select a colour.

    A checkbox will do just fine e.g. ticked for visible, unticked for invisible. A checkbox has a value of true or false respectively.

    Create a checkbox and in the OnClick event put something like:

    If Forms!myform!myCheckbox = True Then
    Forms!myform!mycontrol.Visible = True
    Else
    Forms!myform!mycontrol.Visible = False
    End If

    get the idea...

    Chris

  15. #15
    Join Date
    Sep 2003
    Posts
    35
    Outstanding. Works perfectly. The check itself can be coded as checked = True and unchecked = False. Thanks!

Posting Permissions

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