How to populate form fields from Access table

[terbolee]

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?

Private Sub LoadInfo_Click()

' Pull info out of SQL View.

Dim db As Database
Dim rs As Recordset
Dim tmpUser As String

Set db = Organisations
Set rs = db.OpenRecordset("Select * FROM dbo_Organisations WHERE [Organisation Code] = '" & Organisations.[Organisation Code] & "'")

Invoices![Organisation Type] = rs![Organisation Type]
Invoices!Organisation = rs!Organisation
Invoices![Organisation Phone] = rs![Organisation Phone]
Invoices![Organisation Fax] = rs![Organisation Fax]
Invoices!Department = rs!Department
Invoices!Street = rs!Street
Invoices!Suburb = rs!Suburb
Invoices!State = rs!State
Invoices!Country = rs!Country
Invoices![Contact Title] = rs![Contact Title]
Invoices![Contact First Name] = rs![Contact First Name]
Invoices![Contact Surname] = rs![Contact Surname]
Invoices![Contact Position] = rs![Contact Position]
Invoices![Contact MOB] = rs![Contact MOB]

rs.Close

Set rs = Nothing
Set db = Nothing

End Sub

[MMcCarthy]

Ok, there are a few errors in your code

Private Sub LoadInfo_Click()

' Pull info out of SQL View.

Dim db As Database
Dim rs As Recordset
Dim tmpUser As String

 Set db = [b]CurrentDB  ' This is the database you are currently working on[/b]
 Set rs = db.OpenRecordset("Select * FROM dbo_Organisations " & _ [b]   "[/b]WHERE [Organisation Code] = '" & [b]Me.[Organisation Code][/b] & "'") 
[b]' this will refer to the control on the form so if the control that diplays 
[/b][b]   ' [/b][b]the [/b][b]Organisation Code has a different name use that instead.  [/b]

[b]   ' Me. is how you refer to the current form.[/b]
[b]Me.[/b][Organisation Type] = rs![Organisation Type]
[b]Me.[/b]Organisation = rs!Organisation
[b]Me.[/b][Organisation Phone] = rs![Organisation Phone]
[b]Me.[/b][Organisation Fax] = rs![Organisation Fax]
[b]Me.[/b]Department = rs!Department
[b]Me.[/b]Street = rs!Street
[b]Me.[/b]Suburb = rs!Suburb
[b]Me.[/b]State = rs!State
[b]Me.[/b]Country = rs!Country
[b]Me.[/b][Contact Title] = rs![Contact Title]
[b]Me.[/b][Contact First Name] = rs![Contact First Name]
[b]Me.[/b][Contact Surname] = rs![Contact Surname]
[b]Me.[/b][Contact Position] = rs![Contact Position]
[b]Me.[/b][Contact MOB] = rs![Contact MOB]

 rs.Close

 Set rs = Nothing
 Set db = Nothing

End Sub

The Invoices form must be unbound. That is that there is nothing in the Record Source property.

Mary

[terbolee]

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

Private Sub LoadInfo_Click()

' Pull info out of SQL View.

Dim db As Database
Dim rs As Recordset
Dim tmpUser As String

 Set db = [b]CurrentDB  ' This is the database you are currently working on[/b]
 Set rs = db.OpenRecordset("Select * FROM dbo_Organisations " & _ [b]   "[/b]WHERE [Organisation Code] = '" & [b]Me.[Organisation Code][/b] & "'") 
[b]' this will refer to the control on the form so if the control that diplays 
[/b][b]   ' [/b][b]the [/b][b]Organisation Code has a different name use that instead.  [/b]

[b]   ' Me. is how you refer to the current form.[/b]
[b]Me.[/b][Organisation Type] = rs![Organisation Type]
[b]Me.[/b]Organisation = rs!Organisation
[b]Me.[/b][Organisation Phone] = rs![Organisation Phone]
[b]Me.[/b][Organisation Fax] = rs![Organisation Fax]
[b]Me.[/b]Department = rs!Department
[b]Me.[/b]Street = rs!Street
[b]Me.[/b]Suburb = rs!Suburb
[b]Me.[/b]State = rs!State
[b]Me.[/b]Country = rs!Country
[b]Me.[/b][Contact Title] = rs![Contact Title]
[b]Me.[/b][Contact First Name] = rs![Contact First Name]
[b]Me.[/b][Contact Surname] = rs![Contact Surname]
[b]Me.[/b][Contact Position] = rs![Contact Position]
[b]Me.[/b][Contact MOB] = rs![Contact MOB]

 rs.Close

 Set rs = Nothing
 Set db = Nothing

End Sub

The Invoices form must be unbound. That is that there is nothing in the Record Source property.

Mary

[MMcCarthy]

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

[terbolee]

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

[MMcCarthy]

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

[terbolee]

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

[MMcCarthy]

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?

[terbolee]

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?

[MMcCarthy]

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.

[terbolee]

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.

[MMcCarthy]

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.

Dim strSQL As String

strSQL = "INSERT INTO tablename ([Organisation Type], Organisation, [Organisation Phone], [Organisation Fax], ...) VALUES ('" & Me.[Organisation Type] & "','"& Me.Organisation & "','"& Me.[Organisation Phone] & "','"& Me.[Organisation Fax] & "','"& .....& ");"
DoCmd.RunSQL strSQL

[terbolee]

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.

Dim strSQL As String

strSQL = "INSERT INTO tablename ([Organisation Type], Organisation, [Organisation Phone], [Organisation Fax], ...) VALUES ('" & Me.[Organisation Type] & "','"& Me.Organisation & "','"& Me.[Organisation Phone] & "','"& Me.[Organisation Fax] & "','"& .....& ");"
DoCmd.RunSQL strSQL

[MMcCarthy]

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.

[terbolee]

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.