Adding New Record in SubForm from another subform 1

[ScottMusich]

I will try and explain as best as I can!

I have a form (MainFrm) with two subforms (subform1 and subform2). When I click on a record in subform1 I want a record to be added to subform2. (not actual names!)

I can get the needed data over to subform2 (to whatever record is selected). However I can't get it to create/select a new record. Does anyone know the syntax needed for this?

Any suggestions would be helpful! Scott Musich
Database Analyst/Programmer

 

[WildHare]

Scott, a few more details might help us out here.

1) what do you want added to the record source of Subform2 ? The record you just selected in subform1 ?

2)How are subform1 and subform2 related? If at all?

3) are they both children of the Main form's datasource?

PS - it might also be worth while if you can think of data going into a TABLE, not a form. Makes things clearer, since forms can collect stuff from all over.



78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[ScottMusich]

1) Yes, The selection in subform1 should be entered in subform2

2)They are related by the field that is being selected (LicenseID)

3) No - only one of them are - the Subform1 is actually a listing of possible data to be entered in (needs to be seperate - not combo box) - I am starting to think otherwise as I am typing!

Here are more details:

MainForm is all info about PC - The TAB I am trying to make this work with includes subform1 and subform2

Subform1 is a list of available software licenses that can be added to a PC. Connecting to a query that does some manipulation

Subform2 is the current software on the PC - InstalledSoftwareTbl which is linked to The InventoryTbl by the LicenseID and PCNbr

All I really want though is be able to have code that does the same function as hitting the >* (new record) button on Subform2. I could add the data directly to the table then refresh the data for the form, but would like to be able to do that via the forms instead (always looking for new solutions/ideas).

I am currently doing the following. On the OnClick of a field on subform1:

'want code before this to select new record on subform2

[Forms]![MainForm]![subform2].[Form]![LicenseID] = [Forms]![MainForm]![subform1].[Form]![LicenseID]

'want code afte to refresh subform2

I hope this clears some things up a bit.



Scott Musich
Database Analyst/Programmer

 

[WildHare]

You know what I might do to work this out? I'd write an APPEND query that takes the values from your Subform1 and appends them to the table indicated by subform2. You could tie the query to a command button and just do it.

78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[ScottMusich]

Great idea - it works great - can't beleive I didn't do that in the first place! However, how do I requery subform2 properly after clicking on subform1. Scott Musich
Database Analyst/Programmer

 

[WildHare]

After your operation to run the append query, just add a form refresh for subform2

Docmd.OpenQuery "addThisGuyToSubform2"

Forms!MainForm!{subform2-control}.Form.REFRESH

I'm pretty sure the REFRESH guy will force the (sub)form to reload its' data source.

78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[ScottMusich]

Thanks, This is what I ended up doing:

DoCmd.SetWarnings False

DoCmd.OpenQuery ("appendQry&quot
DoCmd.Requery
[Forms]![mainform]![subform2].Form.Requery

DoCmd.SetWarnings True

The first requery refreshes subform1 and the second one does subform2 - for some reason refresh would not work (not even from the menu)

Again thanks! Scott Musich
Database Analyst/Programmer

 

[WildHare]

Actually, the refresh was probably happening before the query finished...thats the problem with asynchronous processing...

Whatever. It works. More than I can say for me... 78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm