ADO recordsets and Queries

[racquetballguy]

HI

Is there any way while having a data entry form open and using "After Update" to use ADO to open a query that runs the data on the still incomplete open recordset of the form.

rst.MoveLast brings me to the last completed line of data entry, but I want to access the line still being entered.

I can see several work arounds here but I wonder if there is a better way.

Thank You
Ivan

[PEB]

Have you tested recordsetclone?

See Access help for more info about RecordsetClone! :)

[racquetballguy]

Hi

Using recordsetclone didn't help.

In ADO after .MoveLast using:

Me![Something] = rst![somethingelse] returns the last value written to the table.

Me![Something] = [somethingelse] returns the value currently in the form but not yet written to the table.

The problem, I think, is that the sql in the query is based on the TABLE relationships such as the joins. When I run the query based on data already written to the table it works fine. But it does not run based on the data only in the form and not yet written to the table. I am not sure if I can get this to work.

Maybe a query-by-form would be better, though I haven't tried this yet.

Is there a better solution without haveing to close out my current form first?

Thanks
Ivan

[MMcCarthy]

If you could tell us exactly what you are trying to do in the AfterUpdate event you may not need a recordset at all.

Hi

Using recordsetclone didn't help.

In ADO after .MoveLast using:

Me![Something] = rst![somethingelse] returns the last value written to the table.

Me![Something] = [somethingelse] returns the value currently in the form but not yet written to the table.

The problem, I think, is that the sql in the query is based on the TABLE relationships such as the joins. When I run the query based on data already written to the table it works fine. But it does not run based on the data only in the form and not yet written to the table. I am not sure if I can get this to work.

Maybe a query-by-form would be better, though I haven't tried this yet.

Is there a better solution without haveing to close out my current form first?

Thanks
Ivan

[PEB]

Hi Ivan,

It seems that you want to go programatically in your Table to record that is the current record in the Form doesn't you?

So is in your Form the respective ID of the record that is primary key or unique value?

IF Yes, on after Update you have to put:

Dim Mydb AS DataBase
Dim Myr AS recordset
Set mydb=currentDb()
Me.Refresh 'To save the introduced info
Set myr=mydb.openrecordset("SELECT [Your needed Field] FROM [Your needed table] WHERE [Table ID]=" & STR(Your_ID))
IF myr.recordcount=0 then exit function
myr.movefirst
..........

myr.close
mydb.close

Hope this helps!


:)

[racquetballguy]

Hi
I am close, but not quite there yet so let me explain what I am trying to do.

I am trying to create a form where the first 5 fields are user entered and the last 2 fields are calculated while the form is still up - before the user enters the next line of data.

After the last field the user enters I want to use "After Update" to call a query. This query needs to make use of the just entered data and also data previosly entered in three other tables. This query will return an ID Key value from one of the tables(essentai lly a lookup table) and then a module will use this value from the query and some of the data from the fields just entered in the open form to calculate the information for the last 2 fields of the open form.

When I do my query and module seperate from the data entry form and all information is already written to the tables I get the results I want. Implementing this "on the fly" query before my data is written to the tables is giving me the problems.

My initial thought was to use a recordset and .AddNew to force the data to be written to the table but I didn't get that to work. However, in the recordset I can use the data just entered into the form and not yet written to the table, but my query doesn't join on the data from the recordset. It joins on the data from the last line in the underlying form and I haven't gotten it to join to the data in the recordset.

If I give up on the idea of doing this from an open form I can make this work, but I kindof like this set up.

Any suggestions you have would be greatly appreciated.

Thanks
Ivan