Autofill in Datasheet View

[Haunani]

Please help!
I have a form that has the combo box named Room#, the drop down shows three fields: Room#, Location, Space.
Room# is the only one shown on the form, the other two are only viewed in the datasheet view.
I would like to choose the Room# and have the Location and Space autofill for that Room# in the datasheet view.
There are Room#'s that are similar but there is a unique combination of Room#, Location, Space.
Please let me know if there is a way to do this?
I am using Access 2000, I have just taken a Macro and VBA class but can't seem to figure this particular thing out.
Thank you,
Nani

 

[ZanyJanie]

One point that needs to be clarified -- When you refer to “Datasheet View” do you really mean, “when you open the TABLE”?

Forms can also be used to display data in “Datasheet View” and using a form gives you a lot more flexibility to include event code.

To do what you want, create a form with a combo box that includes all 3 fields (e.g. ROOM, LOCATION and SPACE). This fields (I’ll call it cboROOM for this example.) Within your combo box, the ROOM column should be bound to the ROOM field in your table.

On the form , you also need to include the Location and Space fields. (If you want, you set hide and/or lock these fields.)

Next, create and After Update event for your combo box that has the following code.


If (isnull([cboRoom]) THEN
Me.Location = null
Me.Space = null
Else
Me.Location = me.cboRoom.Column(1)
Me.Space = me.cboRoom.Column(2)
End If

Note: The column numbers begin with ZERO, so in the above example Column(1) is actually your second column.

Last of all, in your form properties, set the default default view to "Datasheet"

Hope this helps.