The command button wizard does not allow for a link in 2007

Question

I am building a new database as an update but I am having problems with Command Button Wizard. I have been building Access databases (in 2003) for some years so relationships, common ID fields are all properly accounted for. I have no problem running these in 2007 until I try to re-design a form.

To link to a second form in a new window the command button wizard does not display the main form in the left hand pane (find specific data to display checked), although the new form is shown with all fields showing

Go to www.tuquet.co.uk/file/capture.jpg for a photo of the problem.

For reports it does not show a list of forms to link with to link with.

I have SP2 installed so the issue should have been sorted

I have checked for unbound fields. Use control wizards is checked.

I have re-installed Access but none of these has resolved the problem

My other databases, originally produced in MS2003 work fine, except if I try and re-design the form in any of them then no links work of any kind and the data is corrupted. It also disables all navigation buttons although these are currently working OK in my new database.

What more can I do?

Answer 1

I haven't experienced that either. My first thought is that the Form is unbound hence no bound controls to be listed, but you said you checked that.

But I agree with Graham, this is done with one line of code. Also, In 2007, the wizards create embedded macros, not VBA code, which is much more flexible (in 2007).

Answer 2

Hi Michael

To be honest, I have absolutely no experience with the Access control wizards because I have steadfastly avoided using code-generating wizards for the last 20-plus years that I have been using Access!

I believe that the left-hand pane in your screenshot should display all the controls with values on your current form, and the right-hand pane should display all the fields in the RecordSource of the form you are opening. I cannot explain why, in your case, the left-hand pane is blank.

However, I suggest you simply write the code yourself. It will be much more satisfying, because you will learn more about coding Access VBA, and also your code will be much better that that generated by the wizard :-)

You will need to open the desired form using the DoCmd.OpenForm method.  This method takes a number of arguments (instructions about how to perform the task) and you can read all about them in the offline help, or here: http://msdn.microsoft.com/en-us/library/office/ff820845.aspx

You will probably need to supply values for only the first and fourth arguments (FormName and WhereCondition).

The FormName is straightforward - it is just the name of the form you want to open - for example, "frmGuestInfo".

WhereCondition is a string expression which pre-applies a filter to the form you are opening - for example, "EmployeeID=22".  In your case, you will probably want to construct a string from the name of the field you want to match and some control on your current form that contains the value you want to match it to - For example: "GuestID=" & Me.txtGuestID (where txtGuestID is the name of a textbox on your form).

Putting this together, you have a line of VBA code like this:

DoCmd.OpenForm "frmGuestInfo", , , "GuestID=" & Me.txtGuestID

Put this one line of code into your command button's event procedure like this:

Private Sub cmdShowGuestInfo_Click()

DoCmd.OpenForm "frmGuestInfo", , , "GuestID=" & Me.txtGuestID

End Sub

I hope this is enough to get you going - please post back if you need any further help or clarification.

Best wishes,

Graham