Select data from one form to bring into another

[naan bread]

I have one "Project" form with subform that displays "Contractor" details. The Contractors are stored in a table and they are associated to a table of projects (many to many relationship) through a link table.
When I am in the project form and have the contractor sub form tab "activated" I would like to go to the contractor form (press a button), select a contractor and link the contractor back to the project.
Question:
What is the best way to do this?
I have tried creating a macro but get stuck as I need to "pause" it whilst I select the Contractor... As you can see I am no whizz! What is the simplest way to do this?

 

[Dreamweaver]

Sorry My last reply wont help as What I think you wanna do is open a form to find/add Contractor then have the ID Sent back to the other form if so I'll sort you an example of the simplest way.

mick

 

[naan bread]

I am new to the forum and can only see one reply from you at 6:26. Am I missing something with vonly viewing last reply?

 

[Steve R.]

1. The project table will need a field (call it ZZZ) to hold the contractor's unique id#. I assume that this may already be the case if your tables are linked.
2. Next create a command button that is activated with the on-click-event. Have it transfer the value of the contractor's unique id# on the subform to a control on the main form that is bound to the field (ZZZ) in the project table holds the contractor's unique id#.

Your code would look something like: ZZZ=Forms!Subform1.Form!ContractorID

Note: This approach requires that the contractor field be highlighted before pressing the command button. An alternative to the command button would be to invoke VBA or a macro when you double click on the contractor.

 

[naan bread]

I will need to take some time digesting what you said. Thanks. I am a novice to all this...

I seem to remember creating a macro some time ago along the lines of:

openform...contractor (press button in project window on the subform contractors)

???????????????????????????
(don't know about this bit, but select the contractor in the available records)

gotocontrol...contractor_Id (press button)

runcommand...copy

close form ...contractor

???????????????????????????
(don't know about this bit, but return to project form)

gotocommand...contractor_Id

runcontrol...paste

Am I imagining this?

Thanks

 

[Dreamweaver]

Not sure if this will help as you say subform but at other times you talk off opening a form so this may or may not help?

p.s. Open form 2 first

 

[Dreamweaver]

As A Thought why not use a combo box with a list of Contractors?

 

[naan bread]

thanks dreamweaver. just looking at your example....

 

[naan bread]

combobox might be a good one as we only have a few contractors... thanks

 

[naan bread]

I have tried putting your example (dreamweaver) into a simplified setup of what I have got. It is generating an error I think because I am not sure how to specify which bit of text I am selecting in my form (there are more then one fields).

Private Sub Command5_Click()

DoCmd.OpenForm "Contractor", , , , , acDialog

Me![txtReturn] = Forms!Contractor!txtSend <----- Error

DoCmd.Close acForm, "Contractor", acSaveNo

End Sub

Thanks.

 

[Dreamweaver]

I have tried putting your example (dreamweaver) into a simplified setup of what I have got. It is generating an error I think because I am not sure how to specify which bit of text I am selecting in my form (there are more then one fields).

Private Sub Command5_Click()

DoCmd.OpenForm "Contractor", , , , , acDialog

Me![txtReturn] = Forms!Contractor!txtSend <----- Error

DoCmd.Close acForm, "Contractor", acSaveNo

End Sub

Thanks.

This is the name of the field on the popup form it must match what you have and Only use acSaveNo If your not gonna make changes to the contractor or add a new one Only added to the example to clear the box LOL

 

[naan bread]

I thnk this may be going straight over the top of my head... I have attached what I have tried to do. I am begining to realise I didn't understand what I was doing. What route would you advise taking?

 

[Dreamweaver]

I thnk this may be going straight over the top of my head... I have attached what I have tried to do. I am begining to realise I didn't understand what I was doing. What route would you advise taking?

I'll take a look Back In A Min

 

[Dreamweaver]

You had a number of errors
1 The Fields Names were incorrect and you would have got an error tying to assign the value to the auto number of the contractors table Take a good look at what I've done you've got a lot of work ahead of ya but stick with it as it will feel damn good when it's all working need any more help just shout

Mick

 

[naan bread]

Thank you.

I know there is a lot of work ahead but you've got to start somewhere! Its having half "bits" of knowledge thats the worst! I am sure that is not the last you have heard from me...

 

[naan bread]

I think you were right as the autonumber thing kept throwing before.

Now. for my next trick I am going to post a new thread as I have a different question.... If you feel you have the time I would be very greatful if you could look out for it.

Thanks again.