Open a different form using current record (1 Viewer)

[cjmitton]

I'm new to Access 2010 and creating a business contact DB so eventually people can merge letters from it.

I've started by putting a data input form together and various forms to update which all work fine.

The next part I'm struggling with though, When I create a new business I then want to give the choice to add some contacts, I have both forms ready I just need to link them up using the current saved business (just entered in) to the new form where they can add on the relevent contacts.

All the Database works fine and I have a form with a sub form to view / adjust the business with the various contacts. I just want to make the 'creation' of busibness and contacts a bit slicker!

I hope that makes sense!

 

[John Big Booty]

Welcome to the forum.

You can use the OpenArgs portion of the OpenForm command to pass information between the calling form and the form being opened.

Have a look at the attached for an example of how this might work. Check the On Double Click event of the Combo Box, and the On Load event of the called form.

 

[missinglinq]

...When I create a new business I then want to give the choice to add some contacts, I have both forms ready I just need to link them up using the current saved business (just entered in) to the new form where they can add on the relevent contacts.

You're talking about a 1:Many relationship here, 1 New Business to any number of Contacts. This is the classical scenario that uses a SubForm Control!

The form for entering new businesses would be the main form while the form for adding contacts would be the subform.

In your new company form, add a subform control. When the Wizard comes up choose your Contacts form as the source. If the field name for new companies is the same in both tables, when the Wizard comes up it will automatically link the two using the company name field.

When you go to add a contact in the subform, the company name will automatically be entered.

Linq ;0)>

 

[cjmitton]

Thanks for the replies.

John,
Thanks for the example, I've tried to pull it through in to my DB and I had a few issues coding is sadly not my strong point as I can do a bit of VBA in excel and word from recorded macro's but thats about it. Still I looked up your code and got some more idea's and have got it working. Well nearly!!

Linq,
Thanks for the info about sub forms, I've created one already for ammending / looking up business address & contacts and also for adding more contacts. the main purpose for having to two different forms is that eventually when we add new business we will run through a few screens and having all the information onscreen will be a little confusing for my users. I'm just trying to get ahead so it make life easier for me in the future.

So my issue now is:
On my business add form I have this code:

Private Sub macro_Click()
If Not IsNull(Me!BusinessName) Then
DoCmd.OpenForm "3rd_P_Business_Name_Only", DataMode:=acAdd, OpenArgs:=Me!BusinessName
 Else
  MsgBox "No Business Name"
 End If
End Sub

On the Form it opens I have:

Private Sub Form_Load()
If Me.OpenArgs <> vbNullString Then
   Me.BusinessName = Me.OpenArgs
End If
End Sub

The second form has a sub form in it with the relevent contact fields and a button to save them. The only problem is that when the 'Businessname' is passed through it adds a second record to the business table and links the contacts to that one not the original created in he first?

I'm sure I'm missing something very obvious though!

 

[cjmitton]

On a thought... am I doing this completely wrong?

If I'm entering the data for a business, then saving. should I be using a SQL Query to use the 'businessname' just entered and on screen then call up a new form for client entry? Where the form searchs then opens the business in an 'read-only' format from the 'businessname' provided from the previous form and I can then add the clients.

If so how do I do that?

 

[cjmitton]

So, I've had a play!

Still not got anywhere so I thought that adding the business name to the contacts table may help... Which is kind of did but the link for the ID number does not come through?

I'm completely confused now.

This is part of what will be a bigger DB for clients too but thought I would start easy and gget feedback from staff before I go ahead with the Client aspect. To help you along I've attached my DB for you to look at!