Autofill certain fields in a form

Hi,
I regularly have to fill in forms with inspection data.
On the form, there is a drop down box with the list of inspection locations,
there are then several more boxes that need data inputted, e.g. id numbers,
inspector's name, equipment type etc, that are always the same for each
location.

I have a table with the inspections locations, then the corresponding id
numbers etc in rows. At the moment I am ctrl C ctrl V each bit of data from
the table into the form (each completed form is then exported and recorded
for analysing, the process repeats).

I then input the inspection data into the form, copied from regular emails.

Ideally I would be able to choose the location from the drop down list, then
the various fields with data in automatically fill in. I'm not familiar at
all with Access (new job and all that), but have a fair amount of computer
literacy.

I'm using Access 2003.

Thanks for any help,
Profqwerty

Can we step back for a moment and cover where you need to get to before we
hit how to...?

It sounds like you want to export data (?to what, an Excel spreadsheet,
....?).

It sounds like you have a table that already has all the "associated"
information (you did say that knowing an inspection location guarantees that
you know inspector's name, ... the other fields, right?). If so, then you
can simply store the InspectionLocationID in your (?Inspections) table, and
use a query to join that to the other associated information. You'd then
export the query rather than having to build a "psuedo-table" of data for
export.

Or have I misunderstood?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"profqwerty @hotmail.com" profqwertyremove this wrote in message
...
Hi,
I regularly have to fill in forms with inspection data.
On the form, there is a drop down box with the list of inspection
locations,
there are then several more boxes that need data inputted, e.g. id
numbers,
inspector's name, equipment type etc, that are always the same for each
location.

I have a table with the inspections locations, then the corresponding id
numbers etc in rows. At the moment I am ctrl C ctrl V each bit of data
from
the table into the form (each completed form is then exported and recorded
for analysing, the process repeats).

I then input the inspection data into the form, copied from regular
emails.

Ideally I would be able to choose the location from the drop down list,
then
the various fields with data in automatically fill in. I'm not familiar at
all with Access (new job and all that), but have a fair amount of computer
literacy.

I'm using Access 2003.

Thanks for any help,
Profqwerty

I'm assuming your dropdown box's recordsource is pointed to the inspection
locations table that has all those fields (id number, inspector's name, etc).
If so,

in the AfterUpdate of your dropdown, make and EventProcedure. You're going to
make each of your fields update with data by using the row's information from
the dropdown.

I'm assuming the following order of fields in your dropdown
{idnumber, inspectorname, equipmenttype, etc...}

Private Sub YourDropDownBoxName_AfterUpdate()
Me.IDNumber.Value = Me.YourDropDownBoxName.columns(0)
Me.InspectorName.Value = Me.YourDropDownBoxName.Columns(1)
Me.EquipmentType.Value = Me.YourDropDownBoxName.Columns(2)
End Sub

the part in-between Me. and .Value is the name of the text box who's
controlsource is that field.

Hope this helps.



profqwerty @hotmail.com profqwerty wrote:
Hi,
I regularly have to fill in forms with inspection data.
On the form, there is a drop down box with the list of inspection locations,
there are then several more boxes that need data inputted, e.g. id numbers,
inspector's name, equipment type etc, that are always the same for each
location.

I have a table with the inspections locations, then the corresponding id
numbers etc in rows. At the moment I am ctrl C ctrl V each bit of data from
the table into the form (each completed form is then exported and recorded
for analysing, the process repeats).

I then input the inspection data into the form, copied from regular emails.

Ideally I would be able to choose the location from the drop down list, then
the various fields with data in automatically fill in. I'm not familiar at
all with Access (new job and all that), but have a fair amount of computer
literacy.

I'm using Access 2003.

Thanks for any help,
Profqwerty

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200711/1

Hi,
I have tried the code, but it keeps throwing "method/data member not found"
back at me.

The dropdown box is called "combo80"
so i put:

Private Sub combo80_AfterUpdate()
Me.HS_Number.Value = Me.Combo80.columns(3)
End Sub

I can see how it *should* work, but can't see why it doesn't!

As I fiddle different parts get flagged up!

Is there a way to do this with the expression builder?

Thanks

The VB editor flags up the Private Sub line, but no event seems to work
"benyod79 via AccessMonster.com" wrote:

I'm assuming your dropdown box's recordsource is pointed to the inspection
locations table that has all those fields (id number, inspector's name, etc).
If so,

in the AfterUpdate of your dropdown, make and EventProcedure. You're going to
make each of your fields update with data by using the row's information from
the dropdown.

I'm assuming the following order of fields in your dropdown
{idnumber, inspectorname, equipmenttype, etc...}

Private Sub YourDropDownBoxName_AfterUpdate()
Me.IDNumber.Value = Me.YourDropDownBoxName.columns(0)
Me.InspectorName.Value = Me.YourDropDownBoxName.Columns(1)
Me.EquipmentType.Value = Me.YourDropDownBoxName.Columns(2)
End Sub

the part in-between Me. and .Value is the name of the text box who's
controlsource is that field.

Hope this helps.



profqwerty @hotmail.com profqwerty wrote:
Hi,
I regularly have to fill in forms with inspection data.
On the form, there is a drop down box with the list of inspection locations,
there are then several more boxes that need data inputted, e.g. id numbers,
inspector's name, equipment type etc, that are always the same for each
location.

I have a table with the inspections locations, then the corresponding id
numbers etc in rows. At the moment I am ctrl C ctrl V each bit of data from
the table into the form (each completed form is then exported and recorded
for analysing, the process repeats).

I then input the inspection data into the form, copied from regular emails.

Ideally I would be able to choose the location from the drop down list, then
the various fields with data in automatically fill in. I'm not familiar at
all with Access (new job and all that), but have a fair amount of computer
literacy.

I'm using Access 2003.

Thanks for any help,
Profqwerty

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200711/1

Sorry, I air coded that one. It should be Column, not Columns

Private Sub Combo80_AfterUpdate()

Me.HS_Number.Value = Me.Combo80.Column(3)

End Sub


profqwerty @hotmail.com profqwerty wrote:
Hi,
I have tried the code, but it keeps throwing "method/data member not found"
back at me.

The dropdown box is called "combo80"
so i put:

Private Sub combo80_AfterUpdate()
Me.HS_Number.Value = Me.Combo80.columns(3)
End Sub

I can see how it *should* work, but can't see why it doesn't!

As I fiddle different parts get flagged up!

Is there a way to do this with the expression builder?

Thanks

The VB editor flags up the Private Sub line, but no event seems to work

I'm assuming your dropdown box's recordsource is pointed to the inspection
locations table that has all those fields (id number, inspector's name, etc).
[quoted text clipped - 41 lines]
Thanks for any help,
Profqwerty

--
Message posted via http://www.accessmonster.com