Help with code

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

Guest

I am not a programmer and know very little about VBA. I have copied code and
adapted to my database. I would like to write code that would fill in a
number of fields in my form with data from another form. I have a form that
is called centres with the addresses filled in. I have another form called
personnel where I would like to be able to have a drop down that when I
select a centre the address of the centre would be filled in. If the address
of the personnel is different from the centre, I would then override it. I
looked at the order form in Northwind because they have something similar.
However, its a sold to and ship to and the information is already on the form
in the sold to.

Could someone give me a little help or point me in the direction where
something like this would be explained.

Thank you for any help, it would be greatly appreciated. I am using Access
2003.

Best regards,

dee
 
I have a form that
is called centres with the addresses filled in. I have another form
called personnel where I would like to be able to have a drop down
that when I select a centre the address of the centre would be filled
in. If the address of the personnel is different from the centre, I
would then override it.
Click to expand...

I take it that Centres is actually a table, not a form, since tables
contain data and forms use up screen space. If you want to fill in some
fields on the Personnel form, it would go something like this:

private sub cboCentre_AfterUpdate()

' if there's already an address, then bail out of
' here right now
if not isnull(txtAddress1.Value) then exit sub

' okay, get the relevant address stuff
' since we are returning three fields, it's quicker and
' kinder to write a query than to use DLookup()
dim jetSQL as string
jetSQL = "select addr1, addr2, add3 from centres " & _
"where centreNumber = " & cboCentre.Value

' this gets the data themselves; note the use
' of a forward only snapshot. It's quicker and smaller
' and won't lock out other users
dim rs as Recordset

' using currentdb() here is a bit lazy, but is safe in
' this particular circumstance
set rs = currentdb().OpenRecordset( _
jetSQL, dbOpenSnapshot, dbForwardOnly )

' check that something got returned
if rs.BOF then
' oops, big problem, the Centre was not in the table
msgbox "System Error, please phone Dee now"

else
' fine, copy the database fields into the text boxes
' note that fields and textboxes can contain NULL values
' so we don't need to check for missing values
txtAddress1 = rs!addr1
txtAddress2 = rs!addr2
txtAddress3 = rs!addr3

end if

' all done, close up database object(s)
rs.Close

end sub


Hope that helps


Tim F
 
Back
Top