select record from subform table

[transitman]

Hello everyone,
I have a main form with text boxes that are used to create
new records in the Parts table and displayed in subform1. The part number on the main form shows all duplicates in subform1.(duplicates allowed -- user required) This part works fine...
The PoNumber text box on the main form controls what part
numbers for that PO are shown in subform2. This works fine.
PROBLEM.. I would like to select records from subform1,
copy selected colums and insert that record in the PO table
and have it displayed in subform2(parts for that PO).
I have tried the OnCurrent event.. it always selects the first record shown in subform1. I just can't get the right combination. Thanks in advance for any help

 

[Isadore]

Use the on_click event of Subform1 to get things rolling.

 

[transitman]

This seems to work a little better than the other way I was doing it.. However my code always changes the first record in the PO table instead of adding a new record.. I am using the docmd .... acnewrec... with data saved in global variables..

any other suggestions ????
if you have an example of code that does this, it would help imensely..
thanks again

 

[Isadore]

transit: Little uncertain as to exactly what the problem may be; can you open up a recordset in code and stick the values directly into the desired table, then refresh?

Provide a tad bit more logic and we'll knock this one out. It strikes me a very doable - just need a couple more pieces of business logic.

 

[medium]

I think if you use a recordset(rst) for whatever table, query etc. to which you wish to add a record

Then type

rst.Addnew

This should add a new record to whatever recordset is associated with your rstvariable. This is the first time I've ever been able to give someone else advice.

I'm in agreement with Isadore on the rest.

 

[Isadore]

medium is correct. Here is an example of code that uses ADO to open up a recordset and stick a new record:

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "tblReports", cnn, adOpenStatic, adLockOptimistic
With rstRep
.AddNew
![Report_Num] = Me![RepNo]
![Proj_ID] = Me![ProjID]
...
...
.Update
End With
Set rst = Nothing
Set cnn = Nothing

 

[transitman]

Sorry it took so long to respond to all of you..
I finally got this to work the way I wanted... I used the
click event on subform1 and created an append query that
add the records. I just click on the record I want to copy
which saves 2 columns(PartNo & SeqNo which is an auto number field) and then click on the command button that runs the append query. I do this for every record I need. Works great

I thank you for all your suggestions.