Auto populate form fields from table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I would like to automatically populate fields in a form when the user keys
in an item code.
My form is based on a table (tblDocument) and contains [ItemID], [ItemShort]
and [ItemLong] while my source table (tblMaster) contains the same fields.
When the user enters the item ID number, I would like to copy the short and
long descriptions from master table to the document table. Can anyone advise
the best way to do this?
I am aware that this is not in line with best normalisation practice.
However, we frequently have to modify the fields in the form and then store
the modified text in the document table. Retrieving the data from the master
table just provides a good starting point fo rthe user.
 
Freeflyer said:
Hi,

I would like to automatically populate fields in a form when the user
keys in an item code.
My form is based on a table (tblDocument) and contains [ItemID],
[ItemShort] and [ItemLong] while my source table (tblMaster) contains
the same fields. When the user enters the item ID number, I would
like to copy the short and long descriptions from master table to the
document table. Can anyone advise the best way to do this?
I am aware that this is not in line with best normalisation practice.
However, we frequently have to modify the fields in the form and then
store the modified text in the document table. Retrieving the data
from the master table just provides a good starting point fo rthe
user.
Click to expand...

Use a ComboBox to enter the ItemID and in that ComboBox also include columns for
the ItemShort and ItemLong fields. You can show them in the drop down list or
not (your choice). Then in the AfterUpdate event of the ComboBox have code...

Me.ControlName1 = Me.ComboBoxName.Column(1)
Me.ControlName2 = Me.ComboBoxName.Column(2)

Every time you make a selection in the ComboBox the values from the second and
third columns will be copied to ControlName1 and ControlName2 on your form.
 
Freeflyer said:
Hi,
Click to expand...
I would like to automatically populate fields in a form when the user
keys in an item code.
My form is based on a table (tblDocument) and contains [ItemID],
[ItemShort] and [ItemLong] while my source table (tblMaster) contains
the same fields. When the user enters the item ID number, I would
like to copy the short and long descriptions from master table to the
document table. Can anyone advise the best way to do this?
I am aware that this is not in line with best normalisation practice.
However, we frequently have to modify the fields in the form and then
store the modified text in the document table. Retrieving the data
from the master table just provides a good starting point fo rthe
user.
Click to expand...

Use a ComboBox to enter the ItemID and in that ComboBox also include columns for
the ItemShort and ItemLong fields. You can show them in the drop down list or
not (your choice). Then in the AfterUpdate event of the ComboBox have code...

Me.ControlName1 = Me.ComboBoxName.Column(1)
Me.ControlName2 = Me.ComboBoxName.Column(2)

Every time you make a selection in the ComboBox the values from the second and
third columns will be copied to ControlName1 and ControlName2 on your form.
Click to expand...

I saw this and tried it as well. My problem is that I have a
continuous form, and it changed all the values for that field
(ControlName1) on the continuous form to be whatever was chosen on
that record. How can I make this record specific?

Thanks.
 
Annemarie said:
I saw this and tried it as well. My problem is that I have a
continuous form, and it changed all the values for that field
(ControlName1) on the continuous form to be whatever was chosen on
that record. How can I make this record specific?
Click to expand...

If you set the value of a bound control it is per-record. If you set the value
of an unbound control it will appear on all records.
 
Thanks Rick, while that works I would rpefer to avoid using a combo box. I
intend that the item number field will eventually become a search field and
populate the form with the closest match, so I'd rather keep it all in VBA
now to simplify the improvements later.
Anyway, I ended up going low-tech and turning back to my textbooks and got
it working with the code below. I'm sticking it up here in case it is of any
use to anyone else. (And thanks to John Viescas for most of the code)

Private Sub txtItemNumber_AfterUpdate()
Dim lngItemNumber As Long
Dim rstItem As DAO.Recordset
Dim strSQL As String
' Capture item code entered
lngItemNumber = Me.txtItemNumber
' Fetch item record from Item Master table
Set rstItem = CurrentDb.OpenRecordset("SELECT * FROM tblItem WHERE
ItemNumber = " & lngItemNumber)
' Check item was found
If rstItem.EOF Then
MsgBox "Item not found", vbCritical, "Error"
rstItem.Close
Set rstItem = Nothing
Exit Sub
End If
' Item found, copy data
Me.txtItemShort = rstItem!ItemShort
Me.txtQuantity = 1
Me.txtCostPrice = rstItem!CostPrice
Me.txtListPrice = rstItem!ListPrice
Me.txtMargin = Round((1 - (rstItem!CostPrice / rstItem!ListPrice)), 4)
Me.txtSalePrice = rstItem!ListPrice
Me.txtItemLong = rstItem!ItemLong
' Finished with recordset
rstItem.Close
Set rstItem = Nothing
End Sub

Rick Brandt said:
Freeflyer said:
Hi,

I would like to automatically populate fields in a form when the user
keys in an item code.
My form is based on a table (tblDocument) and contains [ItemID],
[ItemShort] and [ItemLong] while my source table (tblMaster) contains
the same fields. When the user enters the item ID number, I would
like to copy the short and long descriptions from master table to the
document table. Can anyone advise the best way to do this?
I am aware that this is not in line with best normalisation practice.
However, we frequently have to modify the fields in the form and then
store the modified text in the document table. Retrieving the data
from the master table just provides a good starting point fo rthe
user.
Click to expand...

Use a ComboBox to enter the ItemID and in that ComboBox also include columns for
the ItemShort and ItemLong fields. You can show them in the drop down list or
not (your choice). Then in the AfterUpdate event of the ComboBox have code...

Me.ControlName1 = Me.ComboBoxName.Column(1)
Me.ControlName2 = Me.ComboBoxName.Column(2)

Every time you make a selection in the ComboBox the values from the second and
third columns will be copied to ControlName1 and ControlName2 on your form.
Click to expand...
 
Back
Top