Using variables to access a form in Forms Collection

  • Thread starter Thread starter JoshPeltier
  • Start date Start date
J

JoshPeltier

Hi,
I was wondering if anyone could help me. I have written a function
which inserts an address into a table, and returns the new address id
to the calling form. Several forms call the function and have generic
control names. here is the code:

Call addAdrs(me.name)

Public Function addAdrs(frmName As Form)
'adds address based on which form contains the information.
'It then returns the new adrs_id.

Dim rsAdrs As DAO.Recordset
Dim sSQL As String

sSQL = "SELECT * FROM tblAddress"
Set rsAdrs = CurrentDb.OpenRecordset(sSQL)

rsAdrs.AddNew
rsAdrs!street = Forms!frmName!txtStreet
rsAdrs!city = Forms!frmName!txtCity
rsAdrs!State = Forms!frmName!txtState
rsAdrs!zip = Forms!frmName!txtZip
rsAdrs!county = Forms!frmName!txtCounty
rsAdrs.Update

rsAdrs.MoveLast
addAdrs = rsAdrs!adrs_id 'returns the new address id

rsAdrs.Close
Set rsAdrs = Nothing
End Function

This is just the last try. I know that it has to do with my function
argument declaration, and the way I type the form name in the function
call. If you have any ideas, I would appreciate it.

Thanks,
Josh
 
Since frmName is a reference to a form (not the name of it), try dropping
the Forms! in your statements:

rsAdrs.AddNew
rsAdrs!street = frmName!txtStreet
rsAdrs!city = frmName!txtCity
rsAdrs!State = frmName!txtState
rsAdrs!zip = frmName!txtZip
rsAdrs!county = frmName!txtCounty
rsAdrs.Update
 
Back
Top