copying the contents of an autonumber field to a number field

[Ryker]

I have a form and a subform. The main form has order id field which
is an autonumber type. The subform also has order id field which is a
number type. I want the subform order id to have the same value and
the mainform order id. I used the following code set to subform order
id on focus.

Me![order id] = Forms![Enter Work Order]

I get error 2113, the value you entered isn't valid for this field.

How should I code this?

Thanks

[Rich P]

Greetings,

By convention, the mainform will be based on the main table and the
subform will be based on the related detail(s) table(s). If the autonum
field in the main table is the pkID, then the related detail will
(should) have a column which would be the foreign key (fkID) relating to
the main table. The fkID is entered into the detail table when the
detail data is submitted to the detail table.

Ideally, for the detail data, you would have a dataEntry form where the
user enters the data, then you programmaticaly submit this data to your
detail table. The textfields in this dataentry form are basically
unbound fields (not linked to an underlying table). You would have
something like a submit button on this dataentry form, so when the user
is satisfied with the data that was entered into the form, the user
clicks on the submit button - and it is here where your code would add
the fkID to the detail table.

You may be asking now "how do I know what the autonum value is in the
main table?"

upon submission of the data from the dataentry form - there will be a
portion of this data that will go to the main table and the detail data
goes to the detail data. When you submit the data to the main table
"Insert into Main(fld1, fld2, ...) Select '" & txt1 & "', '" & txt2 &
"'..."

you retrieve the latest autonum value as follows - using ADODB -- this
will retrieve the autonum value for the most recently inserted record
(that contains an autonum colum) in the mdb - @@Identity is sort of
global in this case:

Sub GetIdentityValue()
Dim cmd As New ADODB.Command, rs As New ADODB.Recordset
Dim strSQL As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection

cmd.CommandType = adCmdText

strSQL = "SELECT @@Identity"
cmd.CommandText = strSQL

Set rs = cmd.Execute
Debug.Print "*" & rs(0) & "*"

rs.Close

cmd.ActiveConnection.Close
End Sub

When you retrieve the @@Identity value (the AutoNum value) this is what
you would insert into the detail table as the fkID.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Ryker]

Thanks for the info....I will try it.