Transfering Data of a form to a new form

[hexagram]

hi guys can anybody can teach me how to do this scenario

Transfering Data of a form to a new form
The Scenario is:

I have a Delivery Receipt Form (Hardware) with a fields of the following

- id - Formatted as "DR-00000"
- company
- address
- designation
- product
- product line
- etc

It Has a subform of
- qty
- description
- serial
This form is manually inputted to get the data.

Then i have another BLANK form which will be the Pull out Form (Hardware) and have same field as my DR form except for the ID
my ID formatted as "POR - 00000"

I want is in my Pullout Form i have a button (command button) then everytime i click that button it show another form that has the list of my DR number it can be a listbox or even a combo box to see the list of my DR ID and then if choose a certain DR ID the corresponding data for that Delivery Receipt ID will generate in my blank Pull Out Form so that it will automatically show in my Pull Out Form no need for manual entry, thanks for a big help

[MMcCarthy]

I'm not sure from your description exactly what you are looking for but.

You can have a form (frmSearch) with a combobox (cboDRID) and a command button (cmdOpenForm) to open the other form (frmHardware).

Assumptions: Hardware is the name of the table or query.

Set the Record Source of frmHardware to return all records i.e. the Hardware table or appropriate query.
Set the properties of the combobox as follows:
Column Count = 1
Row Source Type = "Table/Query"
Row Source = "SELECT id FROM Hardware ORDER BY id ASC"
Bound Column = 1
Put the following code behind the command button...

Private Sub cmdOpenForm_Click()
   DoCmd.OpenForm "frmHardware", , , "[id]=" & Me.cboDRID
End Sub

Mary

[hexagram]

For simple explanation
Form1(With Data) - DRF 00000 - ID
Form2 (No Data) - POR 00000 - ID
Form3 (List of DRF 00000 ID-

from Form2(No Data) i have a button if i click that button i will go to form3 which is the list of ID (DRF-00000) then from form3 i can pick a certain ID using combox if i choose for id for example DRF-00001 then i click generate i will go now to Form2 again

The Value of DRF 00001 from Form1(with Data) must transfer to Form2(No data)

am i making sense its rili hard for me to explain : ) sorry

[MMcCarthy]

Ok you have to stop thinking of the data as being stored in the forms. They are only interfaces to allow you to view the records.

If you explain why you are doing what you are doing I may be able to help further.

Mary

[hexagram]

hi mary my intial problem is kinda clear for
now my approach is i have a form (POR_FORM) with
a combobox that has a list of DR number
so if i choose a certain DR ID and click my
commandbutton i retrieve the value from other
table (DR_table) it works properly

in lower part of my form i have 3 field
which are particular,qty, and part number
it comes from subform table, how can i retrieve
thier data from a subform table?

DR_Table - ID,Customer,Dat e, ETC..
DR_Detail_Table _Subform - Particular,QTY, Partnumber

POR_FORM - This form is the one retriving data


this is my code in my commandbutton:

Private Sub Command24_Click()
Dim rs As New ADODB.Recordset
Dim cnn As New ADODB.Connection
Dim i As Integer
Dim sSql As String
Set cnn = CurrentProject.Connection
sSql = "select * from [DR_Table] where id =" & Text32.Value
rs.Open sSql, cnn, adOpenKeyset, adLockOptimistic
If rs.RecordCount > 0 Then
Company = rs!Company
Address = rs!Address
Contactperson = rs!Contactperson
Designation = rs!Designation
Telno = rs!Telno
Faxno = rs!Faxno
rs.Close
Else
Company = vbNullString
Address = vbNullString
Contactperson = vbNullString
Designation = vbNullString
Telno = vbNullString
Faxno = vbNullString
End If
End Sub

[MMcCarthy]

DR_Table - ID,Customer,Dat e, ETC..
DR_Detail_Table _Subform - Particular,QTY, Partnumber

What is the relationship between these two tables?

[hexagram]

They have the same DRF ID

[hexagram]

the relationship of
Dr_table to Dr_Detail_Table is one to many

[MMcCarthy]

Try this ...

Private Sub Command24_Click()
Dim rs As New ADODB.Recordset
Dim rs1 As New ADODB.Recordset
Dim cnn As New ADODB.Connection
Dim i As Integer
Dim sSql As String
Set cnn = CurrentProject.Connection
sSql = "select * from [DR_Table] where id =" & Text32.Value
rs.Open sSql, cnn, adOpenKeyset, adLockOptimistic
If rs.RecordCount > 0 Then
Company = rs!Company
Address = rs!Address
Contactperson = rs!Contactperson
Designation = rs!Designation
Telno = rs!Telno
Faxno = rs!Faxno
rs1.Open "SELECT * FROM [Dr_Detail_Table] WHERE [DRF ID]=" & rs![DRF ID], cnn, adOpenKeyset, adLockOptimistic
If rs1.RecordCount > 0 Then
Me.[DR_Detail_Table_Subform].SetFocus
Do Until rs1.EOF
Me.[DR_Detail_Table_Subform]![Particular] = rs1!Particular
Me.[DR_Detail_Table_Subform]![QTY] = rs1!QTY
Me.[DR_Detail_Table_Subform]![Partnumber] = rs1!Partnumber
DoCmd.GoToRecord , , acNewRec
Loop
rs1.Close
rs.Close
Else
Company = vbNullString
Address = vbNullString
Contactperson = vbNullString
Designation = vbNullString
Telno = vbNullString
Faxno = vbNullString
End If
End Sub

Mary

[hexagram]

I put the script then try to run it this is the error

"run time error 3265
item cannot be found in the collection corresponding to the
requested name or ordinal"


rs1.Open "SELECT * FROM [Dr_Detail_Table] WHERE [DRF ID]=" & rs![DRF ID], cnn, adOpenKeyset, adLockOptimisti c

[MMcCarthy]

Try putting it in it's own string first

Dim sSql1 As String

sSQL1 = "SELECT * FROM [Dr_Detail_Table] WHERE [DRF ID]=" & rs![DRF ID]
rs1.Open sSQL1, cnn, adOpenKeyset, adLockOptimistic

Mary

[hexagram]

It still didnt work same error

What is "[DR_Detail_Table _Subform]" in your script - is it the name of the subform table and "[DR ID]" - is it the ID in the field from DR_Table?

In my Table DR ID = ID
and the name of my subform is "DR_Detail_Tabl e"

[hexagram]

Hi mary i already solved the problem and i works properly now thank u for the big help and the idea very very much for the time thanks again... : )

[MMcCarthy]

Hi mary i already solved the problem and i works properly now thank u for the big help and the idea very very much for the time thanks again... : )

You're welcome.