Referencing Tables

[aervin5]

Hi everyone,

In my code, I am able to set a variable equal to a form, but not a table. For example:

dim z as object

set z = Forms("My Form")

However, I cannot set a variable equal to a table....

set z = Tables("My Table") does not work

Does anyone have any advice?

Thanks!

 

[boblarson]

What are you actually trying to accomplish. There may be better ways to do what you are trying to do.

 

[aervin5]

I'm trying to delete all but the last record in a table.

This is what I have so far:

Dim x As Long
Dim y As Long
Dim z As Object

stDocName = "Open Input Table"
DoCmd.RunMacro stDocName

DoCmd.GoToRecord , , acLast
z = Tables("Data Input Table")
y = z.CurrentRecord
x = 1

Do Until x = y
DoCmd.GoToRecord , , acFirst
stDocName = "Delete Macro"
DoCmd.RunMacro stDocName
x = x + 1
Loop

However, the Tables("Data Input Table") reference does not work.

Thanks for your help!

 

[boblarson]

I think you are going about this in the wrong way. First of all, what item identifies the "last" record in the table. Is it the last record entered? You should be aware that access does NOT store records necessarily on when they were entered. The table is not ordered in any particular order (see postings by Pat Hartman on this) and so you should not assume that you can do operations on the "last" record and consistently get what you want to have happen. The only way you can consistently make sure you have the last record added is to utilize some sort of method like an incrementing number (autonumber will work unless you are using replication) or date/time stamp.

In either case, you could actually just use queries to do the work (much faster, if you have a large dataset) instead of iterating through the table.
You could create a single query to pull the latest record added and then use the unmatched query wizard to create a query to pull everything BUT that one record. Then turn the resulting query into a delete query and you can run those at any time.

 

[aervin5]

Thanks

Thanks a lot for your help! I'll try that.