Getting data from form into a SQL statement

  • Thread starter Thread starter Brian C
  • Start date Start date
B

Brian C

I have a form called SINGLE BOOKING AVAILABILITY with
three input field called Date, Combo8 and Combo10 and a
command button.

I want to put the values from these fields into a SQL
statement attached to the command button, i.e.

SELECT AVAILABILITY.DATE, AVAILABILITY.PERIOD,
AVAILABILITY.ROOM, AVAILABILITY.BOOKING ID
FROM AVAILABILITY
WHERE (((AVAILABILITY.DATE)=date from form) AND
(AVAILABILITY.PERIOD)=combo8 from form) AND
(AVAILABILITY.ROOM)=combo10 from form));

How do I do this? How do I get the data out of the form
and into my query once the command button is clicked?

Thanks in advance.
 
I have a form called SINGLE BOOKING AVAILABILITY with
three input field called Date, Combo8 and Combo10 and a
command button.

I want to put the values from these fields into a SQL
statement attached to the command button, i.e.

SELECT AVAILABILITY.DATE, AVAILABILITY.PERIOD,
AVAILABILITY.ROOM, AVAILABILITY.BOOKING ID
FROM AVAILABILITY
WHERE (((AVAILABILITY.DATE)=date from form) AND
(AVAILABILITY.PERIOD)=combo8 from form) AND
(AVAILABILITY.ROOM)=combo10 from form));

How do I do this? How do I get the data out of the form
and into my query once the command button is clicked?

Thanks in advance.
Click to expand...

Use the
forms!FormName!ControlName
syntax.

SELECT AVAILABILITY.DATE, AVAILABILITY.PERIOD,
AVAILABILITY.ROOM, AVAILABILITY.BOOKING ID
FROM AVAILABILITY
WHERE (((AVAILABILITY.DATE)= forms![
SINGLE BOOKING AVAILABILITY]!FormDateFieldName] AND
(AVAILABILITY.PERIOD)=forms![
SINGLE BOOKING AVAILABILITY]![combo8]) AND
(AVAILABILITY.ROOM)=forms![
SINGLE BOOKING AVAILABILITY]![combo10] ));

The form must be open when this query is run.

Two more suggestions.
1) It's not a good idea to use spaces within Access field or Table
names. Make sure you surround the form name with brackets [] (as I
have) so Access knows it's one object name, not 3 individual unknowns.

2) If you really do have a field named [Date] I would strongly suggest
you change it to something else, i.e. dteBookingDate, or dteEventDate,
or simply dteDate.

Date is a Reserved Access/VBA word and should not be used as a
field/object name.
See the appropriate Microsoft KnowledgeBase article for your version
of Access for additional Reserved words.

109312 'Reserved Words in Microsoft Access'
209187 'Acc2000: 'Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
 
Thanks

I tried the following SQL but get a compile error: syntax
error but I can't see what's wrong:

Private Sub Command14_Click()

SELECT AVAILABILITY.BookingDate, AVAILABILITY.Period,
AVAILABILITY.Room, AVAILABILITY.[Booking ID]
FROM AVAILABILITY
WHERE (((AVAILABILITY.Date)=forms![SINGLE BOOKING
AVAILABILITY]![BookingDate]) AND ((AVAILABILITY.Period)
=forms![SINGLE BOOKING AVAILABILITY]![Combo8]) AND
((AVAILABILITY.Room)=forms![SINGLE BOOKING AVAILABILITY]!
[Combo10]));

End Sub

CAn you see the problem?




-----Original Message-----
I have a form called SINGLE BOOKING AVAILABILITY with
three input field called Date, Combo8 and Combo10 and a
command button.

I want to put the values from these fields into a SQL
statement attached to the command button, i.e.

SELECT AVAILABILITY.DATE, AVAILABILITY.PERIOD,
AVAILABILITY.ROOM, AVAILABILITY.BOOKING ID
FROM AVAILABILITY
WHERE (((AVAILABILITY.DATE)=date from form) AND
(AVAILABILITY.PERIOD)=combo8 from form) AND
(AVAILABILITY.ROOM)=combo10 from form));

How do I do this? How do I get the data out of the form
and into my query once the command button is clicked?

Thanks in advance.
Click to expand...

Use the
forms!FormName!ControlName
syntax.

SELECT AVAILABILITY.DATE, AVAILABILITY.PERIOD,
AVAILABILITY.ROOM, AVAILABILITY.BOOKING ID
FROM AVAILABILITY
WHERE (((AVAILABILITY.DATE)= forms![
SINGLE BOOKING AVAILABILITY]!FormDateFieldName] AND
(AVAILABILITY.PERIOD)=forms![
SINGLE BOOKING AVAILABILITY]![combo8]) AND
(AVAILABILITY.ROOM)=forms![
SINGLE BOOKING AVAILABILITY]![combo10] ));

The form must be open when this query is run.

Two more suggestions.
1) It's not a good idea to use spaces within Access field or Table
names. Make sure you surround the form name with brackets [] (as I
have) so Access knows it's one object name, not 3 individual unknowns.

2) If you really do have a field named [Date] I would strongly suggest
you change it to something else, i.e. dteBookingDate, or dteEventDate,
or simply dteDate.

Date is a Reserved Access/VBA word and should not be used as a
field/object name.
See the appropriate Microsoft KnowledgeBase article for your version
of Access for additional Reserved words.

109312 'Reserved Words in Microsoft Access'
209187 'Acc2000: 'Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
Click to expand...
 
Thanks

I tried the following SQL but get a compile error: syntax
error but I can't see what's wrong:

Private Sub Command14_Click()

SELECT AVAILABILITY.BookingDate, AVAILABILITY.Period,
AVAILABILITY.Room, AVAILABILITY.[Booking ID]
FROM AVAILABILITY
WHERE (((AVAILABILITY.Date)=forms![SINGLE BOOKING
AVAILABILITY]![BookingDate]) AND ((AVAILABILITY.Period)
=forms![SINGLE BOOKING AVAILABILITY]![Combo8]) AND
((AVAILABILITY.Room)=forms![SINGLE BOOKING AVAILABILITY]!
[Combo10]));

End Sub

CAn you see the problem?
-----Original Message-----
I have a form called SINGLE BOOKING AVAILABILITY with
three input field called Date, Combo8 and Combo10 and a
command button.

I want to put the values from these fields into a SQL
statement attached to the command button, i.e.

SELECT AVAILABILITY.DATE, AVAILABILITY.PERIOD,
AVAILABILITY.ROOM, AVAILABILITY.BOOKING ID
FROM AVAILABILITY
WHERE (((AVAILABILITY.DATE)=date from form) AND
(AVAILABILITY.PERIOD)=combo8 from form) AND
(AVAILABILITY.ROOM)=combo10 from form));

How do I do this? How do I get the data out of the form
and into my query once the command button is clicked?

Thanks in advance.
Click to expand...

Use the
forms!FormName!ControlName
syntax.

SELECT AVAILABILITY.DATE, AVAILABILITY.PERIOD,
AVAILABILITY.ROOM, AVAILABILITY.BOOKING ID
FROM AVAILABILITY
WHERE (((AVAILABILITY.DATE)= forms![
SINGLE BOOKING AVAILABILITY]!FormDateFieldName] AND
(AVAILABILITY.PERIOD)=forms![
SINGLE BOOKING AVAILABILITY]![combo8]) AND
(AVAILABILITY.ROOM)=forms![
SINGLE BOOKING AVAILABILITY]![combo10] ));

The form must be open when this query is run.

Two more suggestions.
1) It's not a good idea to use spaces within Access field or Table
names. Make sure you surround the form name with brackets [] (as I
have) so Access knows it's one object name, not 3 individual unknowns.

2) If you really do have a field named [Date] I would strongly suggest
you change it to something else, i.e. dteBookingDate, or dteEventDate,
or simply dteDate.

Date is a Reserved Access/VBA word and should not be used as a
field/object name.
See the appropriate Microsoft KnowledgeBase article for your version
of Access for additional Reserved words.

109312 'Reserved Words in Microsoft Access'
209187 'Acc2000: 'Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
Click to expand...
Click to expand...

I misunderstood where you were placing the Select statement.
You can not run a Select query statement directly from form code.
Place the above statement, not in the command button click event on
the form, but in the query itself.

In other words, the query SQL should be what I suggested.

Then in the Form, code the command button to either open the query
(DoCmd.OpenQuery "QueryName"), or to hide the form if the query is
opened from a report (Me.Visible = False).
You haven't said what you are doing.

*** You also haven't changed the field name in ***
WHERE (((AVAILABILITY.Date)=forms! etc.

Should be Where Availability.BookingDate = etc.
 
Thanks for that, it works and displays the record.

However I don't want to show the result

What I then want to do is check the Booking Id field for 1

If it is 1 then I want to link to another form to create a
record in a BOOKING table which is keyed on Booking Id (as
Autonumber) and then update the record on the AVAILABILITY
table with this Booking Id. So I need to get data from the
second form to update the booking table and then update
the availability table.

In essence I have an Availability table with all available
rooms for each period of a school day. I want a teacher to
be able to check availability by looking in the
availability table where a booking id of 1 means
available. Then I want them to enter their details in a
booking form to be entered in a booking table, generate a
booking id and have this put in the availability table to
mark that booking as unavailable.


Booking Table looks like:

Booking Id
Blocked Booked
Teachers Initials
Set Id
Subject
Date Booking entered

Availability table has the 4 fields above

There is a one to many relationship from booking to
availability





-----Original Message-----
Thanks

I tried the following SQL but get a compile error: syntax
error but I can't see what's wrong:

Private Sub Command14_Click()

SELECT AVAILABILITY.BookingDate, AVAILABILITY.Period,
AVAILABILITY.Room, AVAILABILITY.[Booking ID]
FROM AVAILABILITY
WHERE (((AVAILABILITY.Date)=forms![SINGLE BOOKING
AVAILABILITY]![BookingDate]) AND ((AVAILABILITY.Period)
=forms![SINGLE BOOKING AVAILABILITY]![Combo8]) AND
((AVAILABILITY.Room)=forms![SINGLE BOOKING AVAILABILITY]!
[Combo10]));

End Sub

CAn you see the problem?
-----Original Message-----
On Mon, 26 Apr 2004 09:27:45 -0700, Brian C wrote:

I have a form called SINGLE BOOKING AVAILABILITY with
three input field called Date, Combo8 and Combo10 and a
command button.

I want to put the values from these fields into a SQL
statement attached to the command button, i.e.

SELECT AVAILABILITY.DATE, AVAILABILITY.PERIOD,
AVAILABILITY.ROOM, AVAILABILITY.BOOKING ID
FROM AVAILABILITY
WHERE (((AVAILABILITY.DATE)=date from form) AND
(AVAILABILITY.PERIOD)=combo8 from form) AND
(AVAILABILITY.ROOM)=combo10 from form));

How do I do this? How do I get the data out of the form
and into my query once the command button is clicked?

Thanks in advance.

Use the
forms!FormName!ControlName
syntax.

SELECT AVAILABILITY.DATE, AVAILABILITY.PERIOD,
AVAILABILITY.ROOM, AVAILABILITY.BOOKING ID
FROM AVAILABILITY
WHERE (((AVAILABILITY.DATE)= forms![
SINGLE BOOKING AVAILABILITY]!FormDateFieldName] AND
(AVAILABILITY.PERIOD)=forms![
SINGLE BOOKING AVAILABILITY]![combo8]) AND
(AVAILABILITY.ROOM)=forms![
SINGLE BOOKING AVAILABILITY]![combo10] ));

The form must be open when this query is run.

Two more suggestions.
1) It's not a good idea to use spaces within Access
Click to expand...
field
or Table
names. Make sure you surround the form name with
Click to expand...
brackets
[] (as I
have) so Access knows it's one object name, not 3 individual unknowns.

2) If you really do have a field named [Date] I would strongly suggest
you change it to something else, i.e. dteBookingDate,
Click to expand...
or
dteEventDate,
or simply dteDate.

Date is a Reserved Access/VBA word and should not be
Click to expand...
used
as a
field/object name.
See the appropriate Microsoft KnowledgeBase article for your version
of Access for additional Reserved words.

109312 'Reserved Words in Microsoft Access'
209187 'Acc2000: 'Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
Click to expand...
Click to expand...

I misunderstood where you were placing the Select statement.
You can not run a Select query statement directly from form code.
Place the above statement, not in the command button click event on
the form, but in the query itself.

In other words, the query SQL should be what I suggested.

Then in the Form, code the command button to either open the query
(DoCmd.OpenQuery "QueryName"), or to hide the form if the query is
opened from a report (Me.Visible = False).
You haven't said what you are doing.

*** You also haven't changed the field name in ***
WHERE (((AVAILABILITY.Date)=forms! etc.

Should be Where Availability.BookingDate = etc.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
Click to expand...
 
Back
Top