Recordset with Continuous Form

[matzos]

Hello all. Here's my problem:

How can I use the following code in a continuous form? The result of this query gives me 288 records but when I use this as-is in my continuous form, I only get one record...the last one.

dim db as DAO.Database
dim rs as DAO.Recordset
dim strSQL as string

strSQL = "SELECT * FROM (tblProducts INNER JOIN tblDetail ON
tblProducts.ProdID = tblDetail.ProdID)"
set db = CurrentDB()
set rs = db.OpenRecordset (strSQL, dbOpenDynaset)

do until rs.EOF
txtProduct.Value = rs!Product
txtPrice.Value = rs!cost
txtRating.Value = rs!Rating
rs.MoveNext
loop

set db = nothing
set rs = nothing

Thank you for any help you can throw my way!

 

[PJStephenson]

in forms you need to use

DoCmd.GoToRecord acDataForm, "frmFormName", acNewRec

otherwise you are just overwriting your existing data, put it just after "do until rs.EOF"
HTH
Peter

 

[matzos]

Thanks PJ. That SEEMS like the answer I should be looking for but I'm still just getting one record showing the last item in the recordset. BTW - this code is in the Form_Load event.

I tried your code in the beginning of the Loop (as suggested) and at the end with the same result. I tried using acNext instead of acNewRec and that wouldn't work.

I'm drawing a complete blank.

 

[willir]

matzos

I guess we had discussion in another forum. I suspect you are over-writing the same record on the form with each new record in the table.

You can put a STOP in your code and use F8 to walk through your code. Abort it after a few cycles, and I bet you a cyberbear, you will the record you stopped will also be displayed on the form.

Consider something like this...

dim strSQL as string

strSQL = "SELECT * FROM (tblProducts INNER JOIN tblDetail ON
tblProducts.ProdID = tblDetail.ProdID)"

Me.RecordSource = strSQL

Richard

 

[RoyVidar]

I think the continuous form need to be bound some way, either as Willir elaborates on, or you could:

[tt]set me.recordset=rs[/tt]

- i e assign the recordset to the forms recordset, else I think you're probably just going to work on "the same record" (I'd love to be proven wrong on this, though;-))

- and yes, crossposting is kind of frowned upon - have a look at faq181-2886 (#4 addressed this, but I think the whole faq is a good read)

Roy-Vidar

 

[matzos]

I've tried setting the Forms' RecordSource equal to an SQL String and the Forms' Recordset equal to a code recordset but it locks me into the same problem as if I had an Access Query...I can't modify the values.

I have a solution, although I know it's probably not the most elegant solution. I will create a table from the query I am using and link that table to the form so that I can modify the data and then post those changes to the main product table.

I'd still aprreciate any ideas just for the sake of curiousity.

Sorry for the Crosspost but the site was down in some forums and I needed ideas ASAP.

Thanks!

 

[willir]

Yes, and as mentioned in the other post, editing / inserting data from a complex, multitable query can be awkward.

In general, you can edit on the ONE side or the MANY side of an SQL statement. If Access does not know which, neither side can be edited.

Using a record set will not really solve this problem.

Think of it this way...
(Bare with me, since the user profile thing for Tek-Tips is not working at present, I can not review easily review the previous post - I can not remember the specific details)

You want to create a delivery route to deliver pizza, but you have not created the addresses yet. Pretty tough -- you need to create the addresses first, then create the routes.

Like wise, I suspect you need to create the Products first before you create the Details. A typical apprach would be to have main form for products and a subform for details linked on ProdID. First you create the Product in the main form and then create the Details in the subform - not create them both at the same time.

With the creation of a new record, the reasoning is very obvious -- you need the ProdID first, which is generated when you create a product. Then you use the ProdID as the foreign key when creating the detail records.

Richard