subform in VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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

*
 
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
 
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

*
 
Thanks I had found another may of doing it, but this is alot more efficent,
and now I can figure out these bookmarks.
 
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

*
 
Back
Top