Open the recordset in access and use table name in dmax statement

**aman1234** · 05-10-2010, 05:27 AM

Hi folks

I have developed a project in access as a backend and excel as a frontend so all the coding is in excel/vba.
I am writing the following function but becvause I have to refer a table in access database , I need to open the recordset and use that table name in the dmax statement. Please see the code below:

Please Login or Register  to view this content.

I have a problem in code in red colour as tblbatchdetails is a table in access so how can we connect the frontend to this table. If I keed the access database opened while running the userform then it works fine but if the access database is closed and we try to tun the userform then it gives reserved error 2950 in dmax statement.

Thanks for any help.

Aman

**romperstomper** · 05-10-2010, 05:41 AM

What is the point of the recordset if you do that? Why not include a WHERE statement in the SQL rather than just opening the table and select the Max value? In other wordds, something like:

Please Login or Register  to view this content.

**aman1234** · 05-10-2010, 05:56 AM

Thanks for your reply. But I am getting runtime error 3265 "Item cannot be found in the collection corresponding to the requested name or ordinal" in the red line.

Please Login or Register  to view this content.

Can you please how to store the max finish time in a variable so that we can find out the elapsed time.

Thanks
Aman

**romperstomper** · 05-10-2010, 06:04 AM

Your field is probably called MaxOfFinishTime or something similar. You can alias it in the SQL string or use Fields(0) since it's the only field in the recordset.

**aman1234** · 05-10-2010, 06:28 AM

Many thanks for your reply. The code works fine when there are few records in the table and if the table is empty then in gives me an error "Invalid use of null"

Please Login or Register  to view this content.

**romperstomper** · 05-10-2010, 06:30 AM

Check to see if records were returned:

Please Login or Register  to view this content.

**aman1234** · 05-10-2010, 06:39 AM

Again it gives runtime error 94, "Invalid use of null" at

dtmLower = rs.Fields(0)

Thanks

**romperstomper** · 05-10-2010, 06:44 AM

Oh, so you are actually getting a null field:

Please Login or Register  to view this content.

**aman1234** · 05-10-2010, 06:56 AM

It worked gr8. Thanks a lot 4 your help.

Open the recordset in access and use table name in dmax statement

LinkBack

Thread Tools

Rate This Thread

Display

Open the recordset in access and use table name in dmax statement

Re: Open the recordset in access and use table name in dmax statement

Re: Open the recordset in access and use table name in dmax statement

Re: Open the recordset in access and use table name in dmax statement

Re: Open the recordset in access and use table name in dmax statement

Re: Open the recordset in access and use table name in dmax statement

Re: Open the recordset in access and use table name in dmax statement

Re: Open the recordset in access and use table name in dmax statement

Re: Open the recordset in access and use table name in dmax statement

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions