Linking Forms with Command Buttons

[Maxi2011]

Hi Everyone

I may be going about this the wrong way, since I am still pretty new to Access but what I would like to do is link two forms together using a command button.

I have a form which contains all my supplier information and another which contains all my contract information. These are based on tables which are linked by the SUPPLIER ID/SUPPLIER NAME fields.

I know that it would be possible to use a sub-form to have all the suppliers details at the bottom of each contract record. However I'd prefer not to have the data displayed in this way. I would prefer it to be optional - where the user can click a command button on the 'CONTRACTS_FRM' and the correct record on the 'SUPPLIERS_FRM' is opened.

E.g. if viewing Contract details for an agreement with Acme Ltd, the user could click a command button which would then open the supplier information (from 'Suppliers_Tbl' in form view)

Does anyone have any ideas on what the best way to go about this is? or Indeed if this is possible at all?

Thanks!

Maxi2011

 

[maxmangion]

create a command button on your form and put this on the onClick event:

DoCmd.OpenForm "formname", , , "[fieldname]=" & Me.fieldname

 

[Maxi2011]

Error Message

Max

Thanks for the help but I am having a few problems getting it to work. I am getting the error message 'Microsoft Access Cannot find the macro 'Docmd''

Is this something I am doing wrong? Perhaps I am 'filling in the blanks' incorrectly. The form I want the button to be on is called 'Contracts_Frm'. The form it is linking to is 'Suppliers_Frm' and the fields which are related (in the tables at least) are 'Supplier Name' in the 'Contracts_Frm' and 'Supplier ID' in the 'Supplier_Frm'

Thanks again. Appriciate the help!

Maxi2011

 

[Mile-O]

Thanks for the help but I am having a few problems getting it to work. I am getting the error message 'Microsoft Access Cannot find the macro 'Docmd''

Don't put it directily into the line on the event but click the button at the end, select Code Builder, and put the code given in the module that opens up.

 

[Maxi2011]

*Groans at herself*

I'm sorry to be completely useless but I still can't get it working. The form opens now but it opens on a blank version of the suppliers_frm and not the record relating to the contract it should be linked to.

I've tried playing around the with code you gave me to see if I can get it working but it doesn't seem to be recognising that I want to view the specific record relating to the contract.

This is probably something I am doing wrong, any chance someone could break it down into to-dumb-for-words instructions for me?

Thanks guys

Maxi2011

 

[Maxi2011]

VB Code - Can someone check this please?

Ok......

Here's the code I am now trying to use for this. Can anyone tell me why this doesn't set the focus to the supplier record which relates to the contract and how I can correct it please?

Private Sub Forms_Link_Cmd_Click()

On Error GoTo Err_Forms_Link_Cmd_Click

Dim strMsg As String, strTitle As String
Dim intStyle As Integer
Dim strDocName As String, strLinkCriteria As String

' If CompanyName control is blank, display a message.
If IsNull(Me![Supplier Name]) Then
strMsg = "Move to the supplier record whose products you want to see."
intStyle = vbOKOnly
strTitle = "Select a Supplier"
MsgBox strMsg, intStyle, strTitle
Me![Supplier Name].SetFocus
Else
' Otherwise, open Product List form, showing products for current supplier.
strDocName = "Suppliers_frm"
strLinkCriteria = "[Supplier ID] = Forms![Suppliers_frm]![Supplier ID]"
DoCmd.OpenForm strDocName, , , strLinkCriteria
DoCmd.MoveSize (1440 * 0.78), (1440 * 1.8)
End If

Exit_Forms_Link_Cmd_Click:
Exit Sub

Err_Forms_Link_Cmd_Click:
MsgBox Err.Description
Resume Exit_Forms_Link_Cmd_Click

End Sub

'Supplier Name' is the only field common to both tables/forms. However, 'Supplier Name' on the 'Contracts_Frm' is looked-up from 'Suppliers_Frm' - Could this be what's causing it? If it is, is there a way around it?

Thanks Maxi2011

 

[Mile-O]

Private Sub Forms_Link_Cmd_Click()

    On Error GoTo Err_Forms_Link_Cmd_Click

    Const strDocName As String = "Suppliers_frm"
    Const strMsg As String = "Move to the supplier record whose products you want to see."
    Const strTitle As String = "Select a Supplier"
    
    ' If CompanyName control is blank, display a message.
    If IsNull(Me.[Supplier Name]) Then
        MsgBox strMsg, vbOKOnly, strTitle
        Me.[Supplier Name].SetFocus
    Else
        ' Otherwise, open Product List form, showing products for current supplier.
        DoCmd.OpenForm strDocName, , , "[Supplier ID] = " & Forms![Suppliers_frm]![Supplier ID]
        DoCmd.MoveSize (1440 * 0.78), (1440 * 1.8)
    End If
    
    Exit Sub
    
Err_Forms_Link_Cmd_Click:
    MsgBox Err.Description, vbExclamation, Err.Number

End Sub