Search through an Access table

[michael1p]

Hi all

Im creating an access 2000 database and im having a bit of trouble with some functionality. I am not sure how to build a Vb module to search through a table to find if a record already exists in it based on one field(UID). Then give the user an option to delete the record or not. Could you please point me the in the right direction to do this?

Thank you
Michael

 

[Dennisk]

the easiest way would be to use the domain aggregate
DCOUNT()

then use something like this

if DCOUNT(your parameters here) > 1 then
' take some action here
Endif

 

[geekay]

Dim strSQL As String
Dim rs As ADODB.Recordset, Cn As ADODB.Connection
Set rs = New ADODB.Recordset: Set Cn = New ADODB.Connection
Set Cn = CurrentProject.Connection

strSQL = "SELECT tablename.* FROM tablename WHERE tablename.UID = " & ValueOfUID

rs.Open strSQL, Cn, adOpenDynamic, adLockPessimistic

If rs.EOF Then

MsgBox "Inform the user that no records exists"

Else

MsgAns = MsgBox("Ask the user to click Yes to confirm", vbYesNo, "Confirm Deletion")
If MsgAns = vbYes Then
rs.Delete adAffectCurrent
rs.Update

End If

End If

rs.Close

 

[boblarson]

rs.Close

GeeKay: Remember to include garbage collection since VBA doesn't do it automatically -

Set rs = Nothing

Also, if you post code (especially large blocks of code) you should use the code tags to designate it as it will keep code formatting as entered instead of removing extra spaces and all.

The code tags are just the word code enclosed in square brackets and the closing tag just has a forward slash (/) after the first bracket and before the word code.

So, [co.e] and [/co.e] respectively (I replaced the d with a period just for this example so it would show up.

 

[geekay]

Hi bob

Thank you for your comments

I didn't use "Set rs = Nothing" just because rs is a procedure-level variable, the lifetime of which ends when the procedure ends unless it is declared using the key word 'Static' instead of 'Dim'. The statement 'rs.Close' also may be excluded if it is not opened again in the same procedure. When the procedure ends vba releases its storage locations in memory for reuse.

Am I correct bob?

I must say sorry for neglecting the 'Go Advanced' button for posting code here. I am of the notion that readability is more when we post it plain. Anyhow I will do as per your suggestion in future as you are much senior and experienced in this forum.

The sad thing is that the actual beneficiary did not respond at all. Still I do posting because the postings of others like boblarson, ruralguy, ghudson etc., who are active in this forum benefited me a lot and hence it is my obligation to do what I can.

 

[boblarson]

My understanding is that Access is somewhat deficient in garbage collection (I could be wrong, or it could just apply to earlier versions) so I find that it is always good to be explicit in setting any recordset variables to nothing. I do know that they did have a problem with leakage at one time but, as I mentioned, I could be wrong about the later versions (I think A2K is included in the problem children).

 

[geekay]

Sure... No harm in setting record set to Nothing explicitly.

Let me quote from Access Help

Understanding the Lifetime of Variables


The time during which a variable retains its value is known as its lifetime. The value of a variable may change over its lifetime, but it retains some value. When a variable loses scope, it no longer has a value.

When a procedure begins running, all variables are initialized. A numeric variable is initialized to zero, a variable-length string is initialized to a zero-length string (""), and a fixed-length string is filled with the character represented by the ASCII character code 0, or Chr(0). Variant variables are initialized to Empty. Each element of a user-defined type variable is initialized as if it were a separate variable.

When you declare an object variable, space is reserved in memory, but its value is set to Nothing until you assign an object reference to it using the Set statement.

If the value of a variable isn't changed during the running of your code, it retains its initialized value until it loses scope.

A procedure-level variable declared with the Dim statement retains a value until the procedure is finished running. If the procedure calls other procedures, the variable retains its value while those procedures are running as well.

If a procedure-level variable is declared with the Static keyword, the variable retains its value as long as code is running in any module. When all code has finished running, the variable loses its scope and its value. Its lifetime is the same as a module-level variable.

A module-level variable differs from a static variable. In a standard module or a class module, it retains its value until you stop running your code. In a class module, it retains its value as long as an instance of the class exists. Module-level variables consume memory resources until you reset their values, so use them only when necessary.

If you include the Static keyword before a Sub or Function statement, the values of all the procedure-level variables in the procedure are preserved between calls.

 

[boblarson]

Oh, I know what MIcrosoft SAYS it should do, but they've been known to say one thing and yet it really works in a different manner. The info I had received was from experienced users who had experienced the memory leakage firsthand, so I know it occurred, whether it was SUPPOSED TO or not.

So, just to be safe, it is good practice to take care of it yourself so that you know it is happening rather than relying on MS to do it. But, if you don't experience any issues while not doing it then it really isn't a problem. I think it may have been more important when RAM was much smaller and it may be that improvements have been made in that area.

So, anyway, your arguments are valid and I believe that a user can proceed either direction how they see fit. It's just a personal preference thing, unless it affects the operation of the database.

 

[geekay]

Let us conclude

Why should we bother for a line of code for setting an object variable to Nothing. It is a good practice as well. Let us do it where ever necessary. I did the arguments only to highlight the concepts of scope and lifetime of variables to those who are unaware of this, not to bob

 

[modest]

Note on destructor:
You are both right. Access is supposed to destroy these temporary objects when the procedure ends, however Access has had issues and it is always good programming practice to manually free up memory yourself. Pat has specifically defined issues in this forum in other posts (hope they weren't deleted from the hack).

Note on readability:
Code is always more readable when it is formatted and indented correctly - that is, each hiearchial level and iteration should be indented to show where one ends and one begins. You can only achieve this using the code and quote tags.

Despite the misconception, you do not need to "go advanced." You can manually wrap your source code in "code" tags with square brackets around them (i.e. [ code ] w/o the spaces). To end it put a forward slash in it.
Example: [ code ] ....your code here.... [ / code ] (remember to remove the space)
Example: [ color=blue ] ....your text here.... [ / color ] (remember to remove the spaces)

Response to original post:
Here is another way of doing what you're looking for

    [color=Blue]Dim [/color]rs          [color=Blue]As [/color]DAO.RecordSet
    [color=Blue]Dim [/color]db          [color=Blue]As [/color]DAO.Database
    
    [color=Blue]Set [/color]db = CurrentDb
    [color=Blue]Set [/color]rs = db.OpenRecordset("<table name>", dbOpenDynaset)
    
    rs.FindFirst "[UID]=" & <value of UID>
    
    [color=Blue]If [/color][color=Blue]Not [/color]rs.NoMatch [color=Blue]Then[/color]
        [color=Blue]Select [/color][color=Blue]Case [/color]MsgBox("Would you Like To delete UID: " & rs.Fields("UID"), vbYesNo, "Confirm Deletion")
            [color=Blue]Case [/color]vbYes: rs.Delete
        [color=Blue]End [/color][color=Blue]Select[/color]
    [color=Blue]End [/color][color=Blue]If[/color]


    rs.Close
    [color=Blue]Set [/color]rs = [color=Blue]Nothing[/color]
    db.Close
    [color=Blue]Set [/color]db = [color=blue]Nothing[/color]