We help IT Professionals succeed at work.

Access VBA Open table on certain record

Murray Brown
Murray Brown asked
on
2,781 Views
Last Modified: 2013-11-27
Hi

I have two variables oTable, oField and oID

How do I write VBA code to find the record containing oID for field oField intable oTable
and open the table with that record selected

Thanks
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2010

Commented:
Hello murbro,

Why do you want to do this?  Working directly in the tables is usually a bad idea...

Regards,

Patrick
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Hi Patrick

You are right

Sorry I actually should have said in the form for that table.

Thanks

Murray
Hi
I think you have 2 issues.
The first is: How do I find a form based on a tablename. There may be multiple.
The second is: How do I open a form with a record selected.
The first question has no general answer. You may solve this by naming convention. for instance the form you want to open is "frm_" & oTable (I assume that in the code below)
The second question is easy. (at least if you know oid is not a string) Open the form and select the record.
The code below shows you how.
Jan
Sub testOpenform()
    openform 9, "ID", "tbl_config"
End Sub
Sub openform(oID As Long, oField As String, oTable As String)
    Dim oForm As String
    Dim Theform As Access.Form
    oForm = "frm_" & oTable
   
    DoCmd.openform oForm
    Forms(oForm).RecordsetClone.FindFirst oField & "=" & oID & ""
    Forms(oForm).Bookmark = Forms(oForm).RecordsetClone.Bookmark
 
End Sub

Open in new window

This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
thanks
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.