access an unrelated record and change value in field

[ianohlander]

I have been creating a reservations db. I have two related tables, spaces and reservations, both with autonumber ids. The spaces table is related to the reservations table through the reservationID. I have a a parent form called AvailableSpaces, which has the reservationID, customerID and a combo box that selects the type of spaces the user wants. Then there is a subform that is linked to that combo box that displays all the available spaces of that type. The subform is setup so that when one of the spaces listed receives the focus, its spaceID is copied into a text box on the subform. All of that works fine. What I need to do is create a button that, when pressed, sets certain fields of the selected space record, specifically, the reservationID field held in the parent form, and the available field to false (so it doesn't show up in the available query the subform is based on.)

I was thinking along the lines of this:
Me![selectedID]![ReservationID] = Me![ReservationID]

except that the Me![selectedID] doesn't refer to the record in the space table. Neither does:

![Spaces]!Me![SelectedID]. How can I get a reference to that record so as to access its ReservationID field? This has got to be pretty basic to db programming, yet I can't find the answer anywhere. I know FindRecord only seems to work on records the form is based on, and my action is being done in the parent, which isn't based on the spaces table.

I tried:
Dim db As AccessObject
Dim rs As Recordset

Set db = CurrentDb
Set rs = CurrentDb.OpenRecordset("Spaces&quot

With rs
.Seek (Me!selectedID)
![ReservationID] = Me![ReservationID]
![Available] = False
.Update
.Close

End With

inside an event method, and it still doesn't work.
Help me, please. Thank you so much.

(PS: and how can I set up a total that will go through all the rooms in a reservation and add up the expenses so that they'll go on a billing sheet- if they have 1 res, sum one charge, 5 res, sum of 5 charges.)

Thank you.

Ian Ohlander

 

[outsourceoncall]

I am not a VB guy, I can usually do all things through MACROS...in this case, I would use the "SetValue" command in a MACRO... and I can do all things through Christ who stregnthens me, but that's another issue altogether.

 

[ianohlander]

I initially tried using macros, but I kept getting errors with the setValue action in the macro. Specifically, I couldn't tell the computer to set the value of the reservationID field of the specific record referred to by the spaceID (mentioned above). In other words, I wanted to be able to retrieve from the selectedID textbox the spaceID3 of the space I want, access that record, set its reservationID number to that in the reservationID textbox of the form this is in, and set the Available field to "no". In using a macro, I tried using OpenTable, but it visibly opened that table, and I don't want that.

Can you do the second thing with macros? The totalling of the fields in all the Space records who have the same reservationID?

Thank you for any help you can give me.

Ian