Access "Event Procedures"

  • Thread starter Thread starter Roger
  • Start date Start date
R

Roger

I have created an event procedure that looks up the
postcode when the Suburb is selected as follows:

Private Sub SUBURB_AfterUpdate()
Me.POSTCODE = Me.SUBURB.Column(1)
End Sub

I also have a field called ZONES and would like the zone
in a seperate table to be entered when the suburb is
selected, as well as the Postcode.
Can this procedure be added to the existing After update
procedure or if not, how do I get this to work

Thanks for any help
 
Hi Roger,

This should be possible. Click on the ellipses button (the button with the three dots) for the
combo box property that reads Row Source. This should open up the query builder. Add the table
that includes the zone primary key and perhaps a zone description. Make sure that a relationship
exists between the tables, otherwise, you will end up with a Cartesian product result. On the
format tab for the combo box, increase the column count property, and set the column widths
appropriately. If the zone primary key is an autonumber, then you'll likely want to refer to this
column, but leave its width = 0 so that it won't be visible to the user.

Then you'd simply refer to it by column position, keeping in mind that the columns are zero
based:

Private Sub SUBURB_AfterUpdate()
Me.POSTCODE = Me.SUBURB.Column(1)
Me.Zone = Me.SUBURB.Column(x)
End Sub

where x is the appropriate number, likely 2 or 3 depending on which order you added the zone
primary key and zone description field(s).

Tom
____________________________________


I have created an event procedure that looks up the
postcode when the Suburb is selected as follows:

Private Sub SUBURB_AfterUpdate()
Me.POSTCODE = Me.SUBURB.Column(1)
End Sub

I also have a field called ZONES and would like the zone
in a seperate table to be entered when the suburb is
selected, as well as the Postcode.
Can this procedure be added to the existing After update
procedure or if not, how do I get this to work

Thanks for any help
 
I think he said that Zone field was in a seperate table -- meaning it's not
the table bounded to the form, is that correct Roger??

IF so then you would need to open up that other table and write it in
SOMETHING like this:

Private Sub SUBURB_AfterUpdate()
Dim rs as Recordset
Set rs = CurrentDb.OpenRecordset("othertablenamehere")
Me.POSTCODE = Me.SUBURB.Column(1)
rs.AddNew
rs!Zone = Me.SUBURB.Column(x)
rs.Update
rs.close
End Sub
 
Steve,
I think he said that Zone field was in a seperate table
Click to expand...
I understood this--it is in a separate table. I believe your answer is incorrect. Note that the
original poster wants to pull a value from the zone table, and populate a zone field in the
form's current recordset. Your solution proposes to push the POSTCODE value into the zone table.
That's not the task the OP asked to accomplish.

0 for 2 now.

Tom
____________________________________


I think he said that Zone field was in a seperate table -- meaning it's not
the table bounded to the form, is that correct Roger??

IF so then you would need to open up that other table and write it in
SOMETHING like this:

Private Sub SUBURB_AfterUpdate()
Dim rs as Recordset
Set rs = CurrentDb.OpenRecordset("othertablenamehere")
Me.POSTCODE = Me.SUBURB.Column(1)
rs.AddNew
rs!Zone = Me.SUBURB.Column(x)
rs.Update
rs.close
End Sub

--
_______________________
Steve Huff
http://www.huffs.us
Generic email: (e-mail address removed)

____________________________________



Hi Roger,

This should be possible. Click on the ellipses button (the button with the three dots) for the
combo box property that reads Row Source. This should open up the query builder. Add the table
that includes the zone primary key and perhaps a zone description. Make sure that a relationship
exists between the tables, otherwise, you will end up with a Cartesian product result. On the
format tab for the combo box, increase the column count property, and set the column widths
appropriately. If the zone primary key is an autonumber, then you'll likely want to refer to this
column, but leave its width = 0 so that it won't be visible to the user.

Then you'd simply refer to it by column position, keeping in mind that the columns are zero
based:

Private Sub SUBURB_AfterUpdate()
Me.POSTCODE = Me.SUBURB.Column(1)
Me.Zone = Me.SUBURB.Column(x)
End Sub

where x is the appropriate number, likely 2 or 3 depending on which order you added the zone
primary key and zone description field(s).

Tom
____________________________________


I have created an event procedure that looks up the
postcode when the Suburb is selected as follows:

Private Sub SUBURB_AfterUpdate()
Me.POSTCODE = Me.SUBURB.Column(1)
End Sub

I also have a field called ZONES and would like the zone
in a seperate table to be entered when the suburb is
selected, as well as the Postcode.
Can this procedure be added to the existing After update
procedure or if not, how do I get this to work

Thanks for any help
 
Clarification:

My interpretation of the following statement:

"I also have a field called ZONES and would like the zone
in a separate table to be entered when the suburb is
selected, as well as the Postcode."

is that the ZONES field is a foreign key field in the form's current recordset. The OP wants to
populate this field using the primary key value stored in a parent (lookup) table, possibly in a
field of the same name.

Tom
________________________________


Steve,
I think he said that Zone field was in a seperate table
Click to expand...
I understood this--it is in a separate table. I believe your answer is incorrect. Note that the
original poster wants to pull a value from the zone table, and populate a zone field in the
form's current recordset. Your solution proposes to push the POSTCODE value into the zone table.
That's not the task the OP asked to accomplish.

0 for 2 now.

Tom
____________________________________


I think he said that Zone field was in a seperate table -- meaning it's not
the table bounded to the form, is that correct Roger??

IF so then you would need to open up that other table and write it in
SOMETHING like this:

Private Sub SUBURB_AfterUpdate()
Dim rs as Recordset
Set rs = CurrentDb.OpenRecordset("othertablenamehere")
Me.POSTCODE = Me.SUBURB.Column(1)
rs.AddNew
rs!Zone = Me.SUBURB.Column(x)
rs.Update
rs.close
End Sub

--
_______________________
Steve Huff
http://www.huffs.us
Generic email: (e-mail address removed)

____________________________________



Hi Roger,

This should be possible. Click on the ellipses button (the button with the three dots) for the
combo box property that reads Row Source. This should open up the query builder. Add the table
that includes the zone primary key and perhaps a zone description. Make sure that a relationship
exists between the tables, otherwise, you will end up with a Cartesian product result. On the
format tab for the combo box, increase the column count property, and set the column widths
appropriately. If the zone primary key is an autonumber, then you'll likely want to refer to this
column, but leave its width = 0 so that it won't be visible to the user.

Then you'd simply refer to it by column position, keeping in mind that the columns are zero
based:

Private Sub SUBURB_AfterUpdate()
Me.POSTCODE = Me.SUBURB.Column(1)
Me.Zone = Me.SUBURB.Column(x)
End Sub

where x is the appropriate number, likely 2 or 3 depending on which order you added the zone
primary key and zone description field(s).

Tom
____________________________________


I have created an event procedure that looks up the
postcode when the Suburb is selected as follows:

Private Sub SUBURB_AfterUpdate()
Me.POSTCODE = Me.SUBURB.Column(1)
End Sub

I also have a field called ZONES and would like the zone
in a seperate table to be entered when the suburb is
selected, as well as the Postcode.
Can this procedure be added to the existing After update
procedure or if not, how do I get this to work

Thanks for any help
 
Tom,
What exactly does "0 for 2 now" mean?

I'm just trying to help out and I read his question different --
hopefully your comment was not intended to sound as snotty as I read it.

I see how someone could read his question a couple of ways and I'm
simply giving him another option as I read it differently. In either case
it's not worded clearly.

He is already assigning the Postcode field from I assume a list box
called SUBURB when someone clicks on it - I understood his quesiton to say
he has a field called ZONES that is in the SUBURB listbox also that he wants
to assign (push as you call it) back to the ZONES filed which as I
understood it was in another table, granted he would have to go to the
correct record in that table which I didn't inlcude code to do. If I'm
wrong and you are write then great, but otherwise he has two answer to his
poorly worded question.

Have a happy new year.

_______________________
Steve Huff
http://www.huffs.us
Generic email: (e-mail address removed)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combo Boxes 3
Question for John Vinson 5
Sorting address fields 4
Access 2003 1
Access Lookup Postcodes 2
Table/Form Question 3
linking fields 1
Combo boxes Access 2003 1

Back
Top