By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,961 Members | 1,333 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,961 IT Pros & Developers. It's quick & easy.

Transfering Data of a form to a new form

P: 54
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
Mar 12 '07 #1
Share this Question
Share on Google+
13 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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...
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdOpenForm_Click()
  2.    DoCmd.OpenForm "frmHardware", , , "[id]=" & Me.cboDRID
  3. End Sub
  4.  
Mary
Mar 13 '07 #2

P: 54
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
Mar 13 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Mar 13 '07 #4

P: 54
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,Date, ETC..
DR_Detail_Table_Subform - Particular,QTY,Partnumber

POR_FORM - This form is the one retriving data


this is my code in my commandbutton:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command24_Click()
  2. Dim rs As New ADODB.Recordset
  3. Dim cnn As New ADODB.Connection
  4. Dim i As Integer
  5. Dim sSql As String
  6. Set cnn = CurrentProject.Connection
  7. sSql = "select * from [DR_Table] where id =" & Text32.Value
  8. rs.Open sSql, cnn, adOpenKeyset, adLockOptimistic
  9. If rs.RecordCount > 0 Then
  10. Company = rs!Company
  11. Address = rs!Address
  12. Contactperson = rs!Contactperson
  13. Designation = rs!Designation
  14. Telno = rs!Telno
  15. Faxno = rs!Faxno
  16. rs.Close
  17. Else
  18. Company = vbNullString
  19. Address = vbNullString
  20. Contactperson = vbNullString
  21. Designation = vbNullString
  22. Telno = vbNullString
  23. Faxno = vbNullString
  24. End If
  25. End Sub
Mar 13 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
DR_Table - ID,Customer,Date, ETC..
DR_Detail_Table_Subform - Particular,QTY,Partnumber
What is the relationship between these two tables?
Mar 13 '07 #6

P: 54
They have the same DRF ID
Mar 14 '07 #7

P: 54
the relationship of
Dr_table to Dr_Detail_Table is one to many
Mar 14 '07 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
Try this ...

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command24_Click()
  2. Dim rs As New ADODB.Recordset
  3. Dim rs1 As New ADODB.Recordset
  4. Dim cnn As New ADODB.Connection
  5. Dim i As Integer
  6. Dim sSql As String
  7. Set cnn = CurrentProject.Connection
  8. sSql = "select * from [DR_Table] where id =" & Text32.Value
  9. rs.Open sSql, cnn, adOpenKeyset, adLockOptimistic
  10. If rs.RecordCount > 0 Then
  11. Company = rs!Company
  12. Address = rs!Address
  13. Contactperson = rs!Contactperson
  14. Designation = rs!Designation
  15. Telno = rs!Telno
  16. Faxno = rs!Faxno
  17. rs1.Open "SELECT * FROM [Dr_Detail_Table] WHERE [DRF ID]=" & rs![DRF ID], cnn, adOpenKeyset, adLockOptimistic
  18. If rs1.RecordCount > 0 Then
  19. Me.[DR_Detail_Table_Subform].SetFocus
  20. Do Until rs1.EOF
  21. Me.[DR_Detail_Table_Subform]![Particular] = rs1!Particular
  22. Me.[DR_Detail_Table_Subform]![QTY] = rs1!QTY
  23. Me.[DR_Detail_Table_Subform]![Partnumber] = rs1!Partnumber
  24. DoCmd.GoToRecord , , acNewRec
  25. Loop
  26. rs1.Close
  27. rs.Close
  28. Else
  29. Company = vbNullString
  30. Address = vbNullString
  31. Contactperson = vbNullString
  32. Designation = vbNullString
  33. Telno = vbNullString
  34. Faxno = vbNullString
  35. End If
  36. End Sub
Mary
Mar 14 '07 #9

P: 54
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, adLockOptimistic
Mar 14 '07 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
Try putting it in it's own string first
Expand|Select|Wrap|Line Numbers
  1. Dim sSql1 As String
  2.  
  3. sSQL1 = "SELECT * FROM [Dr_Detail_Table] WHERE [DRF ID]=" & rs![DRF ID]
  4. rs1.Open sSQL1, cnn, adOpenKeyset, adLockOptimistic
  5.  
Mary
Mar 14 '07 #11

P: 54
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_Table"
Mar 14 '07 #12

P: 54
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... : )
Mar 14 '07 #13

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Mar 14 '07 #14

Post your reply

Sign in to post your reply or Sign up for a free account.