Can someone pls help me with this code as its driving me nuts...

I have a table named Junction Table1 which has 4 fields: Owner ID, Owner
Card ID, Part1, Part2

I run select query from VBA with 2 parameters getting values from a
listbox (Forms![Owner Card]!lbOwners) and a textbox (Forms![Owner Card]!
txtOwnerCardID)

Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("SELECT [Junction Table1].* FROM
[Junction Table1] WHERE ((([Junction Table1].[Owner ID])=" & Forms![Owner
Card]!lbOwners & ") And (([Junction Table1].[Owner card ID])=" & Forms!
[Owner Card]!txtOwnerCardID & "));")
rs.Edit
rs.Fields("Part1") = CInt(s1) 's1 and s2 are collected from user
rs.Fields("Part2") = CInt(s2)
rs.Update
rs.Close
Set rs = Nothing

Upon inspecting opened recordset it turns out it has only one field
(Owner ID) where it should have 4 fields as i'm selecting all fields from
table. I KNOW SQL STRING IS CORRECT as it works fine in Access 2007!

Help!!!!

hmmm well when I am driven nuts what I do is create generic trial query using
plain old vanilla query design feature...and get it to return the correct
values; in this case depending on the criteria selected in the open Form's
list box/text box...

once I get that working ok - I put that query into sql view and look at the
syntax...

maybe this will help debug...
--
NTC


"jmo" wrote:

> Can someone pls help me with this code as its driving me nuts...
>
> I have a table named Junction Table1 which has 4 fields: Owner ID, Owner
> Card ID, Part1, Part2
>
> I run select query from VBA with 2 parameters getting values from a
> listbox (Forms![Owner Card]!lbOwners) and a textbox (Forms![Owner Card]!
> txtOwnerCardID)
>
> Dim rs As Recordset
> Set rs = CurrentDb.OpenRecordset("SELECT [Junction Table1].* FROM
> [Junction Table1] WHERE ((([Junction Table1].[Owner ID])=" & Forms![Owner
> Card]!lbOwners & ") And (([Junction Table1].[Owner card ID])=" & Forms!
> [Owner Card]!txtOwnerCardID & "));")
> rs.Edit
> rs.Fields("Part1") = CInt(s1) 's1 and s2 are collected from user
> rs.Fields("Part2") = CInt(s2)
> rs.Update
> rs.Close
> Set rs = Nothing
>
> Upon inspecting opened recordset it turns out it has only one field
> (Owner ID) where it should have 4 fields as i'm selecting all fields from
> table. I KNOW SQL STRING IS CORRECT as it works fine in Access 2007!
>
> Help!!!!
>

=?Utf-8?B?TmV0d29ya1RyYWRl?= <(E-Mail Removed)>
wrote in news:F9C6CEEB-83F5-4AED-A770-(E-Mail Removed):

> hmmm well when I am driven nuts what I do is create generic trial
> query using plain old vanilla query design feature...and get it to
> return the correct values; in this case depending on the criteria
> selected in the open Form's list box/text box...
>
> once I get that working ok - I put that query into sql view and look
> at the syntax...
>
> maybe this will help debug...

Actually it WAS designed with query design originally -in Access 2007- and
it worked great. Only when I tested in Access 2003 I found out I had a
problem...

well I have not gone in the reverse direction from 07 to 03 because in
general there can always be libraries/references in the newest version that
an older version does not have nor understand...


--
NTC


"Stan" wrote:

> =?Utf-8?B?TmV0d29ya1RyYWRl?= <(E-Mail Removed)>
> wrote in news:F9C6CEEB-83F5-4AED-A770-(E-Mail Removed):
>
> > hmmm well when I am driven nuts what I do is create generic trial
> > query using plain old vanilla query design feature...and get it to
> > return the correct values; in this case depending on the criteria
> > selected in the open Form's list box/text box...
> >
> > once I get that working ok - I put that query into sql view and look
> > at the syntax...
> >
> > maybe this will help debug...
>
> Actually it WAS designed with query design originally -in Access 2007- and
> it worked great. Only when I tested in Access 2003 I found out I had a
> problem...
>