Welcome to SPN

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

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $98

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
    >>
    >> *
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page