Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

subform in VBA

Discussion in 'Information Technology' started by - Alex, Jul 28, 2006.

Tags:
  1. - Alex

    - Alex
    Expand Collapse
    Guest

    Hello

    I have an Issue using a subform, while using the VBA coding

    The main form contains a List box holding all the users and three buttons,
    delete to delete a user, add, which bring up a window to add one, and exit.
    The Sub form contains three check boxes corresponding to the permissions of a
    form, Edit, Delete, and Add.

    The issue is that when I delete or add a record, the form and subform get
    out of sync. This is due to the fact that the list box does not agree with a
    form subform connection (at least I tried and couldn't work it) so I simply
    coded the record change maually into the form. Here where the problem lies, I
    don't know how to reference the subform in code to move its record along with
    the main form what I have is:

    'i is the index I want to go to, frmAccess is the main form
    DoCmd.GoToRecord acDataForm, "frmAccess", acGoTo, i
    'subUsers is the subform
    DoCmd.GoToRecord acDataForm, "subUsers", acGoTo, i

    When I try to run it it tells me that "The Object subUsers is not open" and
    give a similar error for when I reference the form that the subform
    references.

    Any help would be greatly appreciated.
     
  2. strive4peace

    strive4peace
    Expand Collapse
    Guest

    Hi Alex,

    try this:

    '~~~~~~~~~~~
    '-------- sub form

    Me.subform_controlname.form.RecordsetClone.FindFirst
    "IDfield = " & me.IDfield_controlname

    If Not Me.subform_controlname.form.RecordsetClone.NoMatch Then
    Me.subform_controlname.form.Bookmark =
    Me.subform_controlname.form.RecordsetClone.Bookmark
    End If

    '-------- main form

    Me.RecordsetClone.FindFirst "IDfield = " &
    me.IDfield_controlname

    If Not Me.RecordsetClone.NoMatch Then
    Me.Bookmark = Me.RecordsetClone.Bookmark
    End If

    '~~~~~~~~~~~~~~~~~

    Warm Regards,
    Crystal
    Microsoft Access MVP 2006

    *
    Have an awesome day ;)

    remote programming and training
    strive4peace2006 at yahoo.com

    *

    - Alex wrote:
    > Hello
    >
    > I have an Issue using a subform, while using the VBA coding
    >
    > The main form contains a List box holding all the users and three buttons,
    > delete to delete a user, add, which bring up a window to add one, and exit.
    > The Sub form contains three check boxes corresponding to the permissions of a
    > form, Edit, Delete, and Add.
    >
    > The issue is that when I delete or add a record, the form and subform get
    > out of sync. This is due to the fact that the list box does not agree with a
    > form subform connection (at least I tried and couldn't work it) so I simply
    > coded the record change maually into the form. Here where the problem lies, I
    > don't know how to reference the subform in code to move its record along with
    > the main form what I have is:
    >
    > 'i is the index I want to go to, frmAccess is the main form
    > DoCmd.GoToRecord acDataForm, "frmAccess", acGoTo, i
    > 'subUsers is the subform
    > DoCmd.GoToRecord acDataForm, "subUsers", acGoTo, i
    >
    > When I try to run it it tells me that "The Object subUsers is not open" and
    > give a similar error for when I reference the form that the subform
    > references.
    >
    > Any help would be greatly appreciated.
     
  3. - Alex

    - Alex
    Expand Collapse
    Guest

    Thanks for the help but I'm still having some troubles with this

    I Implemented the code but the findfirst function won't accept the
    parameteres, I'm sending "UserName = JSnake" or "UserName = MDman" it tells
    me that
    "The Microsoft Jet database engine does not recognize 'JSnake' as a valid
    field name of expression." error 3070.

    I looked up the findfirst function, and this should work but heres the
    adjusted code

    strfind = "UserName = " & Me.UserList
    Me.subUsers.Form.RecordsetClone.FindFirst strfind

    sorry, and thanks for any help

    "strive4peace" <"strive4peace2006 at yaho" wrote:

    > Hi Alex,
    >
    > try this:
    >
    > '~~~~~~~~~~~
    > '-------- sub form
    >
    > Me.subform_controlname.form.RecordsetClone.FindFirst
    > "IDfield = " & me.IDfield_controlname
    >
    > If Not Me.subform_controlname.form.RecordsetClone.NoMatch Then
    > Me.subform_controlname.form.Bookmark =
    > Me.subform_controlname.form.RecordsetClone.Bookmark
    > End If
    >
    > '-------- main form
    >
    > Me.RecordsetClone.FindFirst "IDfield = " &
    > me.IDfield_controlname
    >
    > If Not Me.RecordsetClone.NoMatch Then
    > Me.Bookmark = Me.RecordsetClone.Bookmark
    > End If
    >
    > '~~~~~~~~~~~~~~~~~
    >
    > Warm Regards,
    > Crystal
    > Microsoft Access MVP 2006
    >
    > *
    > Have an awesome day ;)
    >
    > remote programming and training
    > strive4peace2006 at yahoo.com
    >
    > *
    >
    >
     
  4. strive4peace

    strive4peace
    Expand Collapse
    Guest

    Hi Alex,

    since UserList is a string, you need to delimit it

    '~~~~~~~~~~~~~~~
    strfind = "UserName = '" & Me.UserList & "'"
    Me.subUsers.Form.RecordsetClone.FindFirst strfind

    If Not Me.subUsers.Form.RecordsetClone.NoMatch Then

    Me.subUsers.Form.Bookmark = _
    Me.subUsers.Form.RecordsetClone.Bookmark

    End If
    '~~~~~~~~~~~~~~~


    Warm Regards,
    Crystal
    Microsoft Access MVP 2006

    *
    Have an awesome day ;)

    remote programming and training
    strive4peace2006 at yahoo.com

    *

    - Alex wrote:
    > Thanks for the help but I'm still having some troubles with this
    >
    > I Implemented the code but the findfirst function won't accept the
    > parameteres, I'm sending "UserName = JSnake" or "UserName = MDman" it tells
    > me that
    > "The Microsoft Jet database engine does not recognize 'JSnake' as a valid
    > field name of expression." error 3070.
    >
    > I looked up the findfirst function, and this should work but heres the
    > adjusted code
    >
    > strfind = "UserName = " & Me.UserList
    > Me.subUsers.Form.RecordsetClone.FindFirst strfind
    >
    > sorry, and thanks for any help
    >
    > "strive4peace" <"strive4peace2006 at yaho" wrote:
    >
    >
    >>Hi Alex,
    >>
    >>try this:
    >>
    >>'~~~~~~~~~~~
    >> '-------- sub form
    >>
    >> Me.subform_controlname.form.RecordsetClone.FindFirst
    >>"IDfield = " & me.IDfield_controlname
    >>
    >> If Not Me.subform_controlname.form.RecordsetClone.NoMatch Then
    >> Me.subform_controlname.form.Bookmark =
    >>Me.subform_controlname.form.RecordsetClone.Bookmark
    >> End If
    >>
    >> '-------- main form
    >>
    >> Me.RecordsetClone.FindFirst "IDfield = " &
    >>me.IDfield_controlname
    >>
    >> If Not Me.RecordsetClone.NoMatch Then
    >> Me.Bookmark = Me.RecordsetClone.Bookmark
    >> End If
    >>
    >>'~~~~~~~~~~~~~~~~~
    >>
    >>Warm Regards,
    >>Crystal
    >>Microsoft Access MVP 2006
    >>
    >> *
    >> Have an awesome day ;)
    >>
    >> remote programming and training
    >> strive4peace2006 at yahoo.com
    >>
    >> *
    >>
    >>
     
  5. - Alex

    - Alex
    Expand Collapse
    Guest

    Thanks I had found another may of doing it, but this is alot more efficent,
    and now I can figure out these bookmarks.

    "strive4peace" <"strive4peace2006 at yaho" wrote:

    > Hi Alex,
    >
    > since UserList is a string, you need to delimit it
    >
    > '~~~~~~~~~~~~~~~
    > strfind = "UserName = '" & Me.UserList & "'"
    > Me.subUsers.Form.RecordsetClone.FindFirst strfind
    >
    > If Not Me.subUsers.Form.RecordsetClone.NoMatch Then
    >
    > Me.subUsers.Form.Bookmark = _
    > Me.subUsers.Form.RecordsetClone.Bookmark
    >
    > End If
    > '~~~~~~~~~~~~~~~
    >
    >
    > Warm Regards,
    > Crystal
    > Microsoft Access MVP 2006
    >
    > *
    > Have an awesome day ;)
    >
    > remote programming and training
    > strive4peace2006 at yahoo.com
    >
    > *
     
  6. strive4peace

    strive4peace
    Expand Collapse
    Guest

    you're welcome, Alex ;) happy to help

    Warm Regards,
    Crystal
    Microsoft Access MVP 2006

    *
    Have an awesome day ;)

    remote programming and training
    strive4peace2006 at yahoo.com

    *

    - Alex wrote:
    > Thanks I had found another may of doing it, but this is alot more efficent,
    > and now I can figure out these bookmarks.
    >
    > "strive4peace" <"strive4peace2006 at yaho" wrote:
    >
    >
    >>Hi Alex,
    >>
    >>since UserList is a string, you need to delimit it
    >>
    >>'~~~~~~~~~~~~~~~
    >> strfind = "UserName = '" & Me.UserList & "'"
    >> Me.subUsers.Form.RecordsetClone.FindFirst strfind
    >>
    >> If Not Me.subUsers.Form.RecordsetClone.NoMatch Then
    >>
    >> Me.subUsers.Form.Bookmark = _
    >> Me.subUsers.Form.RecordsetClone.Bookmark
    >>
    >> End If
    >>'~~~~~~~~~~~~~~~
    >>
    >>
    >>Warm Regards,
    >>Crystal
    >>Microsoft Access MVP 2006
    >>
    >> *
    >> Have an awesome day ;)
    >>
    >> remote programming and training
    >> strive4peace2006 at yahoo.com
    >>
    >> *
     

Share This Page