Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2004
    Location
    Canberra, Australia
    Posts
    13

    Question Unanswered: Newbie - Access Sub-Form Macro Problem

    Apologies for the length - Just want to provide all info.
    I have created the following:
    User_Table comprising UserID (PKey), Name, Ext, Role
    User_Query referencing above.
    A User_Form referencing the query and has an an additional unbound combox field sourced from the UserID. The original UserID_query_Field is invisible (Visible - No)
    A macro
    applyfilter - [User_Query]![UserID] = [Form]![User_Form]![UserID]
    condition Surname is Null action GotoControl [Name]
    condition Surname is not Null action GotoControl [USerID]

    This works brilliantly in the User Form. The macro used on the unbound combo {afterupdate} applies the filter. As the user types the userid it selects the known UserID's. If it is a new user (Name is null) it takes the user to the name field and updates the User table. If it is a known user it displays their current information.

    My problem is this does NOT work when placed as a sub form contained in a Tab page on a MainForm.

    I have discovered that the sub form references fields differently and have changed the references accordingly. i.e. for the [Child],
    applyfilter - [User_Query]![UserID] = [Form]![User_Form]![Child1]![UserID] {not exact sentance} however keep getting the error that it requests a value for USerID i.e. it is not passing the value to the query in the macro.

    My current work around is to OpenForm as a seperate event and refresh the sub-form, Messy.

    Help - How do I pass the value to the macro action query

  2. #2
    Join Date
    Sep 2004
    Location
    Reston, VA
    Posts
    86
    So you have a combo box with a list of UserIDs on your parent form, and a subform that displays the data for the UserID selected in the combo box, right? All you should need to do is link the Child and Master fields.

    Make sure UserID appears on both forms. It already does on the parent form, as a combo box, and add it to the subform if it's not there already (you can set the Visible property to "No" if you don't want to show it, but it still needs to be on the subform). Go to the subform Properties-->Data tab. Enter the name of the UserID combo box control as Link Master Fields, and the name of the UserID control on the subform as Link Child Fields. The subform should then reflect the UserID's data whenever you change the combo box, and you won't need any code.

    Hopefully that helps.

  3. #3
    Join Date
    Oct 2004
    Location
    Canberra, Australia
    Posts
    13
    Thank you, JeffB.
    That worked nicley with the minor addition, that I had to add an macro to refresh the Child form on an afterupdate command.

    Thank you again for a quick reply.

    GregM

  4. #4
    Join Date
    Oct 2004
    Location
    Canberra, Australia
    Posts
    13
    Jeff,
    Just a quick question? What I am trying to do is if the name exist it goes to the next entry field on the mainform. This works through a macro. However I don't seem to be able to pass the focus using the gotocontrol action to the subform surname field even though it correctly detects this field is blank. I keep getting an error message the field doesn't exist. I have tried both the above macro and a vbscript me.{suformname}.{fieldname} but this doesn't work either.
    Can you help?
    Thanks
    Greg

  5. #5
    Join Date
    Sep 2004
    Location
    Reston, VA
    Posts
    86
    Is your subform the datasheet view of a table? On a datasheet I don't think you can set the focus of a field that way, since the field is not a "control". You'd only be able to set the focus for the subform control itself.

    You could do it with a continuous form, since each field is actually a control that you would place in design view. It may work that way. If you are using a continuous form already, then you may want to try this in VBA:

    subform_control_name.Controls("control_name").SetF ocus

    Not sure if that will work, though. Hope some of this helps.

    Jeff

  6. #6
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Quote Originally Posted by gdlkm
    Jeff,
    Just a quick question? What I am trying to do is if the name exist it goes to the next entry field on the mainform. This works through a macro. However I don't seem to be able to pass the focus using the gotocontrol action to the subform surname field even though it correctly detects this field is blank. I keep getting an error message the field doesn't exist. I have tried both the above macro and a vbscript me.{suformname}.{fieldname} but this doesn't work either.
    Can you help?
    Thanks
    Greg
    HI Greg and Welcome to the Forum
    I get that you can move to a certain field dependant on whether another field has the data. But not sure where your SubForm comes in. On the MainForm the user selects the Name in a field and it then moves to the next entry field on the MainForm. Correct? Is that set up in your TabControls for navigation? So, after it leaves the Name field and goes to the next entry field on the MainForm, what is it to now do with the SubForm? Are you trying to make it leap to the SubForm after the MainForm is all filled in?? Just trying to see what's going on.

    have a nice one,
    Bud
    will check back later...niters

  7. #7
    Join Date
    Oct 2004
    Location
    Canberra, Australia
    Posts
    13

    Thumbs up Thanks Bud.. Explanatory

    Thanks for the welcome Bud.
    No not quite. Let me try to explain. I have created a table of user names and details. I have created a form that allows the user to select their userid from a combo box. If the userid exist; surname field is NOT null, it does nothing. If the surname fields is Null, the focus changes to the suranme field and allows the user to input their details and updates the data table. This works as a standalone User Form. My problem is that the User details are secondary to the purpose of the database. A document record system. The main form is the "document" record. What I am trying to do is create a main form comprised of three tab forms. One tab is the users details. By having an unbound combo box (Row source to the USerID) called say 'Author_Input_Text' on the tab and a sub-form (Child##) containing the User information form I am trying to recreate the same actions as for the standalone form. The above information from Jeff it does update the sub form, although with some glitches, i.e. a blank entry causes me all sorts of bother. When an existing user is entered, it recognises the NOT null and will move to what ever the next field on the main form is.However, where I am having real problems is trying to get the focus into the sub-form if the surname field is blank. Currently, it tries to update the main form (which I don't particularly want as it will not be correctly completed at this time) and still won't move the focus!

    This help?

    Look forward to any answers to this riddle. As you may have gathered my db knowledge is somewhat old fashioned and my love for Access just grows in appreciation each day... :

  8. #8
    Join Date
    Oct 2004
    Location
    Canberra, Australia
    Posts
    13

    Smile WhooHoo

    I figured out the sub-form problem. Thanks Jeff, Bud.

    I had the validation set to not null for the title field. Changed it and the sub form works nicely. However, it now "Creates" an impartial record.

    Oh well next problem.

    Should only require an undo function... (I shall now run screaming into the night)

    Jeff,
    the exact syntax was
    Me.Subform_Field/Control_name.Controls("Field/Control_name").SetFocus
    works a treat.

    Thanks again.

  9. #9
    Join Date
    Oct 2004
    Location
    Canberra, Australia
    Posts
    13

    Smile WhooHoo

    I figured out the sub-form problem. Thanks Jeff, Bud.

    I had the validation set to not null for the title field. Changed it and the sub form works nicely. However, it now "Creates" an impartial record.

    Oh well next problem.

    Should only require an undo function... (I shall now run screaming into the night)

    Jeff,
    the exact syntax was
    Me.Subform_Field/Control_name.Controls("Field/Control_name").SetFocus
    works a treat.

    Thanks again.

  10. #10
    Join Date
    Sep 2004
    Location
    Reston, VA
    Posts
    86
    Glad you got it figured out.

Posting Permissions

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