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

How to populate form fields from Access table

P: 23
I have two tables: Organisations & Invoices. I have a form called Invoices that is used to enter data into the invoices Table. When entering data, I'd like to be able to type into the [Organisation Code] field, and then click on the ! button to run an SQL script....which will populate some of the fields on the form. Having read discussions here, I've tried some scripting but it does not work. Not sure if I'm on the right track even. Can you help?


Expand|Select|Wrap|Line Numbers
  1. Private Sub LoadInfo_Click()
  2.  
  3. ' Pull info out of SQL View.
  4.  
  5. Dim db As Database
  6. Dim rs As Recordset
  7. Dim tmpUser As String
  8.  
  9. Set db = Organisations
  10. Set rs = db.OpenRecordset("Select * FROM dbo_Organisations WHERE [Organisation Code] = '" & Organisations.[Organisation Code] & "'")
  11.  
  12. Invoices![Organisation Type] = rs![Organisation Type]
  13. Invoices!Organisation = rs!Organisation
  14. Invoices![Organisation Phone] = rs![Organisation Phone]
  15. Invoices![Organisation Fax] = rs![Organisation Fax]
  16. Invoices!Department = rs!Department
  17. Invoices!Street = rs!Street
  18. Invoices!Suburb = rs!Suburb
  19. Invoices!State = rs!State
  20. Invoices!Country = rs!Country
  21. Invoices![Contact Title] = rs![Contact Title]
  22. Invoices![Contact First Name] = rs![Contact First Name]
  23. Invoices![Contact Surname] = rs![Contact Surname]
  24. Invoices![Contact Position] = rs![Contact Position]
  25. Invoices![Contact MOB] = rs![Contact MOB]
  26.  
  27. rs.Close
  28.  
  29. Set rs = Nothing
  30. Set db = Nothing
  31.  
  32. End Sub
Jan 15 '07 #1
Share this Question
Share on Google+
15 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Ok, there are a few errors in your code

Expand|Select|Wrap|Line Numbers
  1. Private Sub LoadInfo_Click()
  2.  
  3. ' Pull info out of SQL View.
  4.  
  5. Dim db As Database
  6. Dim rs As Recordset
  7. Dim tmpUser As String
  8.  
  9.  Set db = CurrentDB  ' This is the database you are currently working on
  10.  Set rs = db.OpenRecordset("Select * FROM dbo_Organisations " & _    "WHERE [Organisation Code] = '" & Me.[Organisation Code] & "'") 
  11. ' this will refer to the control on the form so if the control that diplays 
  12.    ' the Organisation Code has a different name use that instead.  
  13.  
  14.    ' Me. is how you refer to the current form.
  15. Me.[Organisation Type] = rs![Organisation Type]
  16. Me.Organisation = rs!Organisation
  17. Me.[Organisation Phone] = rs![Organisation Phone]
  18. Me.[Organisation Fax] = rs![Organisation Fax]
  19. Me.Department = rs!Department
  20. Me.Street = rs!Street
  21. Me.Suburb = rs!Suburb
  22. Me.State = rs!State
  23. Me.Country = rs!Country
  24. Me.[Contact Title] = rs![Contact Title]
  25. Me.[Contact First Name] = rs![Contact First Name]
  26. Me.[Contact Surname] = rs![Contact Surname]
  27. Me.[Contact Position] = rs![Contact Position]
  28. Me.[Contact MOB] = rs![Contact MOB]
  29.  
  30.  rs.Close
  31.  
  32.  Set rs = Nothing
  33.  Set db = Nothing
  34.  
  35. End Sub
  36.  
The Invoices form must be unbound. That is that there is nothing in the Record Source property.

Mary
Jan 15 '07 #2

P: 23
Many thanks for your help. I've made the syntax corrections. I unbound the Invoices form (Record Source) and now all the fields display #Name? and prevents me from typing anything. Any suggestions? Thanks.

================================================== =

Ok, there are a few errors in your code

Expand|Select|Wrap|Line Numbers
  1. Private Sub LoadInfo_Click()
  2.  
  3. ' Pull info out of SQL View.
  4.  
  5. Dim db As Database
  6. Dim rs As Recordset
  7. Dim tmpUser As String
  8.  
  9.  Set db = CurrentDB  ' This is the database you are currently working on
  10.  Set rs = db.OpenRecordset("Select * FROM dbo_Organisations " & _    "WHERE [Organisation Code] = '" & Me.[Organisation Code] & "'") 
  11. ' this will refer to the control on the form so if the control that diplays 
  12.    ' the Organisation Code has a different name use that instead.  
  13.  
  14.    ' Me. is how you refer to the current form.
  15. Me.[Organisation Type] = rs![Organisation Type]
  16. Me.Organisation = rs!Organisation
  17. Me.[Organisation Phone] = rs![Organisation Phone]
  18. Me.[Organisation Fax] = rs![Organisation Fax]
  19. Me.Department = rs!Department
  20. Me.Street = rs!Street
  21. Me.Suburb = rs!Suburb
  22. Me.State = rs!State
  23. Me.Country = rs!Country
  24. Me.[Contact Title] = rs![Contact Title]
  25. Me.[Contact First Name] = rs![Contact First Name]
  26. Me.[Contact Surname] = rs![Contact Surname]
  27. Me.[Contact Position] = rs![Contact Position]
  28. Me.[Contact MOB] = rs![Contact MOB]
  29.  
  30.  rs.Close
  31.  
  32.  Set rs = Nothing
  33.  Set db = Nothing
  34.  
  35. End Sub
  36.  
The Invoices form must be unbound. That is that there is nothing in the Record Source property.

Mary
Jan 15 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Many thanks for your help. I've made the syntax corrections. I unbound the Invoices form (Record Source) and now all the fields display #Name? and prevents me from typing anything. Any suggestions? Thanks.
You need to unbind all the controls as well. Check each of the textboxes as they probably still have their control source set to the field. Set them all blank.

Mary
Jan 15 '07 #4

P: 23
Yes, I see the controls in the text boxes. I think I understand what you're recommending. I was hoping to use the form as data filter as well. I guess, this means that I can create another form just for the filters.

The Invoice form contains over 65 fields with 14 fields to be automatically populated. Do I unbound just the 14 fields or for all? If I unbound all the fields and later fill in the rest of the fields, will Access give me the option to save the record to the correct table? Thanks.

================================================== =

You need to unbind all the controls as well. Check each of the textboxes as they probably still have their control source set to the field. Set them all blank.

Mary
Jan 15 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Yes, I see the controls in the text boxes. I think I understand what you're recommending. I was hoping to use the form as data filter as well. I guess, this means that I can create another form just for the filters.

The Invoice form contains over 65 fields with 14 fields to be automatically populated. Do I unbound just the 14 fields or for all? If I unbound all the fields and later fill in the rest of the fields, will Access give me the option to save the record to the correct table? Thanks.
You asked for a form where you could set the values and this is what your code would have done.

However, I think what you actually want is a form to return the records but one where you can go to a specific record based on the user entering it's operation code.

What doesn't make sense is you have one form with 65 fields but you are trying to change/move only 14 of them. You can't do this. The form is just a window to view records in the table or query. What exactly are you trying to do?

Mary
Jan 15 '07 #6

P: 23
I want to use the form to add new records into the invoices table. I'm hoping that, once I type the Organisation Code (1st field in the form), that my code will populate 14 fields in the form...from the Organisations table.

Once the 14 fields are populated, I can proceed to fill in the rest of the fields and then save it as a new record. This record is later mail merged with Word.

Hope this makes more sense. Perhaps I'm going about it in a totally wrong way. I got the mail merge working. I was hoping that my code could simplify data entry.

Any suggestions? BTW, many thanks for your help.


===============================================

You asked for a form where you could set the values and this is what your code would have done.

However, I think what you actually want is a form to return the records but one where you can go to a specific record based on the user entering it's operation code.

What doesn't make sense is you have one form with 65 fields but you are trying to change/move only 14 of them. You can't do this. The form is just a window to view records in the table or query. What exactly are you trying to do?

Mary
Jan 16 '07 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
I want to use the form to add new records into the invoices table. I'm hoping that, once I type the Organisation Code (1st field in the form), that my code will populate 14 fields in the form...from the Organisations table.

Once the 14 fields are populated, I can proceed to fill in the rest of the fields and then save it as a new record. This record is later mail merged with Word.

Hope this makes more sense. Perhaps I'm going about it in a totally wrong way. I got the mail merge working. I was hoping that my code could simplify data entry.

Any suggestions? BTW, many thanks for your help.
How is your Invoice number being generated if this is a new invoice?
Jan 16 '07 #8

P: 23
For a new invoice, the invoice number will be the Organisation Code+yymm. The yymm will be derived from the [Invoice Date] field.

Any suggestions? Thanks.

How is your Invoice number being generated if this is a new invoice?
Jan 17 '07 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
For a new invoice, the invoice number will be the Organisation Code+yymm. The yymm will be derived from the [Invoice Date] field.

Any suggestions? Thanks.
Invoices are usually based on an Autonumber Primary key field in the Invoices table.
Jan 17 '07 #10

P: 23
Hi Mary

Using your advise, I finally managed to populate the fields on my form. Many thanks for taking me this far.

This allows me to fill in the rest of the fields so I can save the fields as a record into my table. However, with the fields being Unbound, is there a way to save?

Thanks.

Invoices are usually based on an Autonumber Primary key field in the Invoices table.
Jan 17 '07 #11

MMcCarthy
Expert Mod 10K+
P: 14,534
Hi Mary

Using your advise, I finally managed to populate the fields on my form. Many thanks for taking me this far.

This allows me to fill in the rest of the fields so I can save the fields as a record into my table. However, with the fields being Unbound, is there a way to save?

Thanks.
You will need to run an INSERT statement to add a new record or if you have already created a blank record with just the invoice number then an UPDATE statement.

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3. strSQL = "INSERT INTO tablename ([Organisation Type], Organisation, [Organisation Phone], [Organisation Fax], ...) VALUES ('" & Me.[Organisation Type] & "','"& Me.Organisation & "','"& Me.[Organisation Phone] & "','"& Me.[Organisation Fax] & "','"& .....& ");"
  4. DoCmd.RunSQL strSQL
  5.  
Jan 17 '07 #12

P: 23
In my case, it's probably the INSERT statement, since all the fields are now filled...ready to be saved.

Just wondering where to place this code.

Should the code go in between:

======
Private Sub Detail_Click()

End Sub
======

Thanks.


You will need to run an INSERT statement to add a new record or if you have already created a blank record with just the invoice number then an UPDATE statement.

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3. strSQL = "INSERT INTO tablename ([Organisation Type], Organisation, [Organisation Phone], [Organisation Fax], ...) VALUES ('" & Me.[Organisation Type] & "','"& Me.Organisation & "','"& Me.[Organisation Phone] & "','"& Me.[Organisation Fax] & "','"& .....& ");"
  4. DoCmd.RunSQL strSQL
  5.  
Jan 17 '07 #13

MMcCarthy
Expert Mod 10K+
P: 14,534
In my case, it's probably the INSERT statement, since all the fields are now filled...ready to be saved.

Just wondering where to place this code.

Should the code go in between:

======
Private Sub Detail_Click()

End Sub
======

Thanks.
You can put it wherever you want the event to happen. Button click event sounds fine.
Jan 17 '07 #14

P: 23
Hi Mary

Many thanks for all your help. Due to my weak coding skills, I find this project to be a rather tedious one for me. Yes, I've learned a lot but there's still so much to learn. Is it possible that I could send you my files along with what I'd like to see happen so that you could fix my codes?

I hope what I'm about to mention is not against the forum's rules but I would gladly reimburse you for your time. Please let me know if that's alright with you?

Thanks


You can put it wherever you want the event to happen. Button click event sounds fine.
Jan 18 '07 #15

MMcCarthy
Expert Mod 10K+
P: 14,534
Hi Mary

Many thanks for all your help. Due to my weak coding skills, I find this project to be a rather tedious one for me. Yes, I've learned a lot but there's still so much to learn. Is it possible that I could send you my files along with what I'd like to see happen so that you could fix my codes?

I hope what I'm about to mention is not against the forum's rules but I would gladly reimburse you for your time. Please let me know if that's alright with you?

Thanks
My fees as a private consultant are quite high whereas my assistance here along with many other experts is free.
Jan 21 '07 #16

Post your reply

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