Populate form fields from Access table using VBA

[terbolee]

I have two tables: Invoices & Organisations. A form called Invoices contains a field [Organisation Code] that is used to populate about 14 fields in the 65-fields form. The form is unbound (no Record Source) & so are its fields. With some help, I've gone as far as to develop the following VBA code but I'm not sure how to run it. I'd appreciate any help you can throw my way, please.

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

Private Sub LoadInfo_Click()

' Pull info out of SQL View.

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

Set db = CurrentDb
Set rs = db.OpenRecordset("Select * FROM dbrganisations " & _
"WHERE [Organisation Code] = '" & Me.[Organisation Code] & "'")

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

rs.Close

Set rs = Nothing
Set db = Nothing

End Sub

Private Sub Invoice_Period_To_AfterUpdate()

Me.[Invoice Period To] = Me.[Invoice Period From] + Me.[Days in Period]

End Sub

 

[namliam]

Not really help but...
Dim db As Database
Set db = CurrentDb
Set db = Nothing

Why set something you dont need, waste of memory and resources... Simply do :
Set rs = Currentdb.OpenRecordset
And be done with it.

Your database, probably not a choice anymore... but you really should not use spaces in field names!

About Me. and Me!, use one or the other and stick with it. I would recomend using Me.

About the real help....
It looks like this code is intended to run in an "On Click" event of a button... That button would be called "LoadInfo"

I hope all this helps (a little)

 

[quiksilver1804]

You forgot

rs.Update
rs.Close

Try that!

 

[namliam]

No he didnt, he is not updating the RS, he is updating the form.
If he were updating the RS, he also forget to RS.Edit

 

[dsigner]

Just for my own education!
If there are 14 fields from one source then why not use this as the form source? You can still update theother 51 as you do at the moment.

 

[terbolee]

Wow! Thanks for the info. Basically, the form is used to add new records. By typing an [Organisation Code], I'm hoping that 14 fields would be automatically filled in from the Organisation table...a great time saver. After that, I can fill in the other fields and save the record. The Invoices table is mail merged with a Word document.

Having read this, am I on the right path?

The Mailman mentioned a button. Is this a Macro button? And would I copy the code into the body of the Macro?

I'm a bit uneasy about the Unbound bit. I was asked to unbound the form but would this still let me save the new record into Invoices?

Advice please and many thanks.

 

[terbolee]

That's precisely what I'm trying to do. By typing a code in the [Organisation Code] field in the form, I'm trying to populate 13 other fields from a record in a table. I can then complete the rest of the fields and save as a new record. Am I on the right track? Thanks.

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

Just for my own education!
If there are 14 fields from one source then why not use this as the form source? You can still update theother 51 as you do at the moment.

 

[terbolee]

Thanks for your help. When I finally get the code working, my guess is that I should create a macro for it. I'd like to know how I can create a macro and add the button to the toolbar, please. Thanks.

Not really help but...
Dim db As Database
Set db = CurrentDb
Set db = Nothing

Why set something you dont need, waste of memory and resources... Simply do :
Set rs = Currentdb.OpenRecordset
And be done with it.

Your database, probably not a choice anymore... but you really should not use spaces in field names!

About Me. and Me!, use one or the other and stick with it. I would recomend using Me.

About the real help....
It looks like this code is intended to run in an "On Click" event of a button... That button would be called "LoadInfo"

I hope all this helps (a little)

 

[namliam]

Yes you seem to be on the right track...

 

[stopher]

Thanks for your help. When I finally get the code working, my guess is that I should create a macro for it. I'd like to know how I can create a macro and add the button to the toolbar, please. Thanks.

Regarding how to run your code, yes you could add a button, but probably neater is to put the code in the After Update event for the Organisation Code field. Then when the user changes the Org. code, the After Update event will run and populate your other fields.

You put the code in the After Update event as follows:
- open the properties for the Organisation Code field
- Click on the After Update line
- Click on the three dots to the right of the line and select code builder
- Copy your code into the space between the Private and End statements
(obviously don’t copy the old Private and End statements)


As and aside, there are other ways to do what you want. I tend to create a join query to included the lookup data so then the extra fields are available to the form. I’d be interested to know from other contributors about the pros and cons of both methods.

Another method is using the DLookup() function but the can cause performance issues particularly if you have several fields.

Stopher

 

[dsigner]

There seems to be an awful lot going on here and I'm not sure that i am following all of it. Basically once you have got all the info on the form you then have to save it out to one or more tables. Because it (or some of it) is unbound you are going to have to save it thruogh one or more record sets. I am not the best one to advise on this as there are at least two different types of recordsets. I just use which ever I happen to have a working version of. This is very dirty coding. So you need the one appropriate to your version of access and stick to it. Someone who understands these things will douybtless tell you how.
Good luck.

 

[stopher]

Because it (or some of it) is unbound you are going to have to save it thruogh one or more record sets.

I assumed that terbolee was not trying to save the unbound data but simply wanted it shown on the form for the benefit of the user. Any attempt to save the unbound data in question here (organisational data) goes against the grain of relational database design since the data is already available in another table and referenced via the "organisational code" foreign key.

Stopher

 

[terbolee]

Many thanks, Dsigner. You've been very helpful.

There seems to be an awful lot going on here and I'm not sure that i am following all of it. Basically once you have got all the info on the form you then have to save it out to one or more tables. Because it (or some of it) is unbound you are going to have to save it thruogh one or more record sets. I am not the best one to advise on this as there are at least two different types of recordsets. I just use which ever I happen to have a working version of. This is very dirty coding. So you need the one appropriate to your version of access and stick to it. Someone who understands these things will douybtless tell you how.
Good luck.

 

[terbolee]

Hi Stopher. I realise that I'm going against the grain of relational design but I find the need to save the form with some duplicate data. Once saved into the Invoices table, MS-Word is used to mail merge with the table to print out the invoices.

Many thanks for the info about the After Update event. I will apply it and let you know how it went.

I'm also of the understanding that MS-Word mail merging with "two" tables is going to be difficult.

Cheers

I assumed that terbolee was not trying to save the unbound data but simply wanted it shown on the form for the benefit of the user. Any attempt to save the unbound data in question here (organisational data) goes against the grain of relational database design since the data is already available in another table and referenced via the "organisational code" foreign key.

Stopher

 

[terbolee]

Phew! Many thanks for the assurance!

Yes you seem to be on the right track...

 

[stopher]

Hi Stopher. I realise that I'm going against the grain of relational design but I find the need to save the form with some duplicate data. Once saved into the Invoices table, MS-Word is used to mail merge with the table to print out the invoices.

....

I'm also of the understanding that MS-Word mail merging with "two" tables is going to be difficult.

You don't need to save the data in one table and indeed should not store the data in one table. This really is a bad way to go and will destroy any integrity in your database. I really can't stress how important this point is.

Create a simple join query to include your two tables. Use this query as your mail merge source. It will make your data appear as a combined single table.

Is there any reason why you are not creating a report in Access rather than exporting to Word?

Stopher

 

[terbolee]

Hi Stopher. Except for a Compile error and data member not found, the Update is trying to run. I'd appreciate it if you could look at the code below and let me know if it's doing what I think it should be doing, please:

==========
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * FROM dbrganisations " & _
"WHERE [Organisation Code] = '")

Me.Organisation = rs.Organisation
Me.[Organisation Phone] = rs.[Organisation Phone]
Me.[Organisation Fax] = rs.[Organisation Fax]
===========

I'm assuming that rs is the incoming data from the Organisations table and that Me is the current form. In the form is a [Organisation Code] field where I type the code. This code will then be used to match a single record in the Organisations table. The code above is supposed to copy into the form, the following fields.

I'd appreciate you help please. Thanks.

Regarding how to run your code, yes you could add a button, but probably neater is to put the code in the After Update event for the Organisation Code field. Then when the user changes the Org. code, the After Update event will run and populate your other fields.

You put the code in the After Update event as follows:
- open the properties for the Organisation Code field
- Click on the After Update line
- Click on the three dots to the right of the line and select code builder
- Copy your code into the space between the Private and End statements
(obviously don’t copy the old Private and End statements)


As and aside, there are other ways to do what you want. I tend to create a join query to included the lookup data so then the extra fields are available to the form. I’d be interested to know from other contributors about the pros and cons of both methods.

Another method is using the DLookup() function but the can cause performance issues particularly if you have several fields.

Stopher

 

[namliam]

I'm also of the understanding that MS-Word mail merging with "two" tables is going to be difficult.

More so, it is quite impossible... But you can (like Stopher said) use a query to make a "virtual" table and merge word with the query.

Member not found means that you have a name for a object that doesnt excist, either in the RS or on the form.

One thing I like to point out tho. If you are using unbound controls, in general it is not smart to use the same names as the fields in the tables. This makes it look like it is bound.

Use something like [ubOrganisation Phone] to make sure everyone that looks at this knows it is an unbound control. Naming conventions are REALLY REALLY REALLY important.

Then as a seconday problem
Set rs = db.OpenRecordset("Select * FROM dbrganisations " & _
"WHERE [Organisation Code] = '")
You are not getting any data as you are not putting anyting into the Where of this recordset.