Form Code for Combo Box - Table Lookup

[aaronjmack]

I am working on a church database that has three separate tables each with an AutoNumbered Primary Key, with one table containing the values for the other two table's keys. I need some help with the code that will allow these three tables to work together in a form, including the proper relationships between the tables and the select statements inside the Requests table design.....

I appologize for this being long, but didn't know how else to show what I have. Thank you for any help on any portion of this!

FORM STRUCTURE W\ CODE (TABLE STRUCTURE AT BOTTOM)
-----------------------------------------------------------
------------------------THIS SECTION WORKS-----------------
Message Log (Form):
User enters data into these fields on the form:
Title
Recorded
Srvc_Month
Srvc_Date
Srvc_Year
Srvc_Day
Srvc_Time
Users clicks a "Save" Command button that writes the data into an auto-numbered (Service_ID) record.

------------------THIS SECTION WORKS-----------------------
Address Book (Form):
(Works just like the Message Log Form, but uses the Recipients table, auto-numbering the Recipient_ID record).

--------------------THIS SECTION WORKS---------------------
Request (Form):
User enters data into these fields on the form:
Requester
Recipient
Rqst_Month
Rqst_Date
Rqst_Year
Rqst_Day
Rqst_Time
-------------------NEED A LOT OF HELP HERE-----------------
User clicks a "Check Status" Command button that I would like to perform the following:
On Click
If Form.Rqst_Month=Services.Srvc_Month and
Form.Rqst_Date=Services.Srvc_Date and
Form.Rqst_Year=Services.Srvc_Year and
Form.Rqst_Day=Services.Srvc_Day and
Form.Rqst_Time=Services.Srvc_Time
Then
Put Services.Service_ID into Requests.Service_ID
for the current record, matching the above.
GoTo Set_Status
Else
Set Note.Value to "No Service Found."
GoTo Set_Recipient
Set_Status:
If Recorded=Yes where Requests.Service_ID =
Services.Service_ID
Then
Set Note.Value to "Service Recorded."
GoTo Set_Recipient
Else
Set Note.Value to "Service Not Recorded."
GoTo Set_Recipient
Set_Recipient:
Put Recipients.Recipient_ID into
Requests.Recipient_ID for the current record,
where Form.Recipient=Recipients.Name
GoTo Exit_Set
Exit_Set
Exit Sub

----------------FOR REFERENCE------------------------------
TABLE STRUCTURE
-----------------------------------------------------------
Services (Table)
Service_ID (AutoNumber);(Primary Key)
Title (Text Data);(Text Box)
Recorded (Yes/No);(Check Box)
Srvc_Month (Text Data);(Combo Box from Month_Box.Month)
Srvc_Date (Text Data);(Combo Box from Date_Box.Date)
Srvc_Year (Text Data);(Combo Box from Year_Box.Year)
Srvc_Day (Text Data);(Combo Box from Day_Box.Day)
Srvc_Time (Text Data);(Combo Box from Time_Box.Time)

Recipients (Table)
Recipient_ID (AutoNumber);(Primary Key)
Name (Text Data);(Combo Box from Name_Box.Name)
Address (Text Data);(Text Box)
Phone (Text Data);(Text Box)

Requests (Table)
Request_ID (AutoNumber);(Primary Key)
Service_ID (Number Data);(HELP - SQL Select Statement)
Recipient_ID (Number Data);(HELP - SQL Select Statment)
Recipient (Text Data);(Combo Box from Name_Box.Name)
Requester (Text Data);(Combo Box from Name_Box.Name)
Rqst_Month (Text Data);(Combo Box from Month_Box.Month)
Rqst_Date (Text Data);(Combo Box from Date_Box.Date)
Rqst_Year (Text Data);(Combo Box from Year_Box.Year)
Rqst_Day (Text Data);(Combo Box from Day_Box.Day)
Rqst_Time (Text Data);(Combo Box from Time_Box.Time)
Note (Text Data);(Text Box)

---------------------END-----------------------------------