Access SQL

[pechoi]

Hi,
I am kind of new at Access (pretty good with VBA I think :). I am
trying to retrieve data using SQL statement. I have a table with 5
field: ID (Access given), Card1, Card2, Group, Point.
I want to see other values based on Card1 and Card2. I think I can get
record with OpenRecordSet. I can print Count on that object and returns
1 (I think it should be). But i don't know how to use them. I tried
!Group but it is giving me error (I did with .FindFirst but it only
filter by one condition not multiple one) I got code:

Private Sub Card1_AfterUpdate()
Dim lsCard1 As String
Dim lsCard2 As String
Dim mySQL As String
Dim loDB As DAO.Database
Dim loRSPlayer As DAO.Recordset
Set loDB = CurrentDb()

lsCard1 = Card1.ItemData(Card1.ListIndex)
lsCard2 = Card2.ItemData(Card2.ListIndex)
'Get SQL
mySQL = "SELECT HandGroup.[Group] from HandGroup where Card1 = " &
lsCard1 _
& " AND Card2 = " & lsCard2

Set loRSPlayer = loDB.OpenRecordset(mySQL)

If Not loRSPlayer.EOF Then Me.Bookmark = loRSPlayer.Bookmark
End Sub
Thx

[David W. Fenton]

pe****@syr.edu wrote in
news:11**********************@u72g2000cwu.googlegr oups.com:

I am kind of new at Access (pretty good with VBA I think :). I am
trying to retrieve data using SQL statement. I have a table with 5
field: ID (Access given), Card1, Card2, Group, Point.
I want to see other values based on Card1 and Card2. I think I can
get record with OpenRecordSet. I can print Count on that object
and returns 1 (I think it should be). But i don't know how to use
them. I tried !Group but it is giving me error (I did with
.FindFirst but it only filter by one condition not multiple one)
I got code:

I don't understand what the point of the recordset is or where
you're using this. Is it in a form? If so, then create a filter that
filters the form, then you can browse the records that match the
filter. You may find the IN ([sql]) operator useful, as it will
leave the recordset of the form editable.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[pechoi]

David W. Fenton wrote:

pe****@syr.edu wrote in
news:11**********************@u72g2000cwu.googlegr oups.com:

I am kind of new at Access (pretty good with VBA I think :). I am
trying to retrieve data using SQL statement. I have a table with 5
field: ID (Access given), Card1, Card2, Group, Point.
I want to see other values based on Card1 and Card2. I think I can
get record with OpenRecordSet. I can print Count on that object
and returns 1 (I think it should be). But i don't know how to use
them. I tried !Group but it is giving me error (I did with
.FindFirst but it only filter by one condition not multiple one)
I got code:

I don't understand what the point of the recordset is or where
you're using this. Is it in a form? If so, then create a filter that
filters the form, then you can browse the records that match the
filter. You may find the IN ([sql]) operator useful, as it will
leave the recordset of the form editable.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Could you please let me know how to do that? I want to do that in VBA
since I got 2 comboboxes setting that filter. ANd also how can I access
that record? FindFirst?

Thx

[David W. Fenton]

pe****@syr.edu wrote in
news:11**********************@g10g2000cwb.googlegr oups.com:

David W. Fenton wrote:

pe****@syr.edu wrote in
news:11**********************@u72g2000cwu.googlegr oups.com:

> I am kind of new at Access (pretty good with VBA I think :). I
> am trying to retrieve data using SQL statement. I have a table
> with 5 field: ID (Access given), Card1, Card2, Group, Point.
> I want to see other values based on Card1 and Card2. I think I
> can get record with OpenRecordSet. I can print Count on that
> object and returns 1 (I think it should be). But i don't know
> how to use them. I tried !Group but it is giving me error (I
> did with .FindFirst but it only filter by one condition not
> multiple one) I got code:

I don't understand what the point of the recordset is or where
you're using this. Is it in a form? If so, then create a filter
that filters the form, then you can browse the records that match
the filter. You may find the IN ([sql]) operator useful, as it
will leave the recordset of the form editable.

Could you please let me know how to do that? I want to do that in
VBA since I got 2 comboboxes setting that filter. ANd also how can
I access that record? FindFirst?

If it's the form's recordset, you don't have to do anything but use
the navigation buttons in the form.

To filter by two combo boxes, you'll have to have code in the
AfterUpdate events of the combo boxes that check the values of both
of them. Or, you could have a FILTER command button that does it.
You'd then write an appropriate SQL WHERE clause and you can either
change the form's .Recordsource property to the new filtered SQL
statement, or you can set the filter property of the form to the
WHERE clause.

An IN statement would be something like:

SELECT * FROM MyTable
WHERE ID IN (SELECT ID FROM OtherTable WHERE [criteria])

The statement inside the IN () is a full SQL statement returning one
column, the results of which are used as the criteria for the outer
WHERE clause.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/