VB open form on a specific record

Question

I am using Microsoft access and i want to open a form to a specific id when a button is clicked. Can i specify the id in the Do.open form command. the code below opens a form but then a dialog box opens asking to enter the id . anyone any ideas ?

Private Sub button1_Enter()
Dim recordID As Integer
recordID = Me.CurrentRecord
DoCmd.OpenForm "Form3", , , "ID = recordID"
End sub

Answer 1

First, change:

recordID = Me.CurrentRecord

To:

recordID = Me.ID

Where Me.ID is the name of a field or control on the current form ( Microsoft Access - get record id when button is clicked ).

When you refer to a variable, put it outside the quotes:

DoCmd.OpenForm "Form3", , , "ID = " & recordID

This is going to be fine for an ID, which is numeric, but it will get a little more complicated with text and dates, because you will need delimiters. This will work well as long as sometextvar does not contain a quote:

DoCmd.OpenForm "Form3", , , "Atext = '" & sometextvar & "'"

Otherwise

DoCmd.OpenForm "Form3", , , "Atext = '" & Replace(sometextvar,"'","''") & "'"

And dates take #

DoCmd.OpenForm "Form3", , , "ADate = #" & somedatevar & "#"

To avoid problems with locales, it is nearly always best to format to year, month, day, so:

DoCmd.OpenForm "Form3", , , "ADate = #" & Format(somedatevar,"yyyy/mm/dd") & "#"