Using select Statments to filter a subform

[scjohn]

Can some one please help me out. I have a database for customers to reserve certain locations. before they can reserve a location they have to check if it is available for the day requested. Im using a query right now for the customers to use, but they complain its to complicated. what i would like to do is to have a Option Group, listing the locations, a calendar for them to select the date from, in a subform all event names would be listed along with the times. i know this is possible but i have no idea how to do this. can some one please help. if im not making this clear enough please tell me.

 

[daveUK]

Try using a listbox to display the results rather than a subform. The rowsource of the listbox would be the query. The user could select the location and date, press a command button (Check Date or something similar), which would run the query and you could then requery the listbox to ensure the relevant results are displayed.

HTH

 

[scjohn]

Thats great, thanks. my problem is with writing the code or SQL behind the option group and calendar. any suggestions?

 

[Liv Manto]

Get the values form the list box and calendar,

put them in a variable.

First create your query with a sample data

then copy the sql, put it into a string variable




then on a command button, make the sql as the recordset


on click

me.recordsource = sqlstring


make sure your sql syntax is with the variable. WHat I do is I put first check the syntak by a msgbox

so for example

sqlstring

sqlstring = "SELECT Count(AtoNote.TASKID) AS CountOfTASKID FROM AtoNote LEFT JOIN NWCDistinct ON AtoNote.NoteNWCID = NWCDistinct.NWCID GROUP BY AtoNote.TASKID HAVING AtoNote.TASKID = "

sqlstring = sqlstring & CurrentTaskID

sqlstring = sqlstring & " ORDER BY AtoNote.TASKID"

msgbox sqlstring.




Note the CurrentTasKID is an integer variable.

If its a string , second line should be

sqlstring = sqlstring & "'" & CurrentTaskID & "'"


(single quotes included)


Tell me if it works.

 

[scjohn]

ok, i understant what you are saying. could you tell me how to reference the option buttons to the table. there are 7 locations that i would like users to be able to select from.

John

 

[Liv Manto]

Private Sub NewItemFrame_Click()

Dim NewItemFrameValue As Integer

NewItemFrameValue = Me.NewItemFrame.Value '(The chosen option

dim mytext as string


Select Case NewItemFrameValue


Case 1 ' if the value of my option group is 1 -- make sure you number your options.

mytext = "Locale1"

Case 2

mytext = "'Locale2"

Case 3 '
mytext = "Locale3"





End select

 

[scjohn]

I still need more help. I dont understant how the buttons in the Option Group reference the table. I am attatching the form that i am trying to complete for reference. please help.

 

[scjohn]

can someone recommend a example that i could follow? any help would be greatly appriciated.

John