Pass Value from 1 form to create record in another

[iankerry]

Hi all

Hope you can help.

I have two table films and filmcopies ( i often have duplicates of the same film).

The filmcopies table has an id field which tells it which film it is (filmID) this is how they are linked.

When i get a new film i need to add it to the films database, save it, then i want to open the filmcopies form and automatically create a new record with the new filmID already in the appropriate filmcopies field.

hope that makes sense.

thanks, Ian

 

[Tanya]

you need another table to relate the two tables you have already. Like this

tblFilmCopyRecord
FilmCopyRecordID - PK
FilmID - FK
FilmCopyID - FK

You then need to relate the tables. Once you have done this, creating a form to enter your data is relatively simple.

Hope this helps.

 

[pbaldy]

Use DoCmd.OpenForm with the appropriate argument to open in data entry mode, then:

Forms!SecondFormName.FilmID = Me.FilmID

adjusting the form and control names as appropriate.

 

[iankerry]

Hi Both,

thanks very much for your replies. Really appreciate it.

I still haven't got it to work.

So I am on my form newfilm and am adding a new film name with a filmID (autonumber.) I have a command button which i click to save the record and then launch the [copy form] so that i can create a new record with the filmID in it.

on the command button i have :

DoCmd.RunCommand acCmdSave
DoCmd.OpenForm "copy form"

I get an error on the save if it is a new record, but if i go back to the 'new'record it is ok.

And then before the "copy form" loads i get:

r/t error 2465 application defined or object defined error

The code on the load event is:
Forms![copy form].tblFilms_ID = Forms!newfilm.dbo_Films.ID (though i have tried other lines too with no success.


I think what i want is simple - i want the ID of the new film to be passed to a new record in a different table. Am I being dull?

Thanks again.

ian

 

[Tanya]

Here is a sample of what I think you want, you can work with how it looks.

Let me know how you go with it.

cheers
Tanya

 

[iankerry]

Thanks Tanya, I'll take a look at that now!

 

[Tanya]

The sample I have loaded takes advantage of navigation buttons to achieve what you want, it is the easiest way to navigate.
the form also allows you to add new films. It can be made more user friendly with combo box's for ease of searching a particular title if you wish.

 

[Tanya]

Another version. After re-reading your first message

 

[iankerry]

Wow Tanya, thanks a mill.

I have been trying to recreate your setup into mine, but there are of course complications!

would it be OK to upload a sample of my database so you can see what i am up against?

best,

ian

 

[Tanya]

Yep, no prob

 

[iankerry]

have spent ages trying to get my dabase small enough to upload and by the time it was small enough, not enough of it works. so i am back to square one at the moment!

i'll be back...

 

[Tanya]

Try
'Tools>Database Utilities>Compress and Repair'

 

[Tanya]

oops... you are using 2007, I only have 2003. You will need to save it in an earlier version


You could also try uploading to this site for free

http://www.savefile.com/

 

[iankerry]

great stuff - good link. try this:

http://www.savefile.com/files/1963828

thanks again.

ian

 

[Tanya]

See how you go with this copy Ian, I can't do any more tonight its too late. Don't know what was wrong with your version, couldn't get it to cooperate.

I've given you two versions of form setup. You can choose which suits.

You will have to import your data across and tidy up the forms.

Hope this gets you out of trouble.

cheers
Tanya

 

[iankerry]

Thanks Tanya. really appreciate your time. sleep well.

Ian

 

[georgedwilkinson]

Hi iankerry,

I'm not sure if you resolved this yet. I believe that you need to take Paul's advice in post #3 and reverse it.

You can programmatically manipulate a control on the parent form something like this:

Forms!FirstFormName.FilmID = Me.FilmID

Obviously, there may be a need to write much more complex code, depending on what it is you're doing.

Depending on how you're doing all this, you may want to refresh your parent form, too.

I don't really log on very much any more so make sure to shout out to anyone if you need followup information.