Results 1 to 10 of 10

Thread: VB Program

  1. #1
    Lounger
    Join Date
    Jul 2002
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VB Program

    I've got a database in Access 97. It is a database with employee names, their employee numbers and their first day and last day worked until 1994. Then data was appended from 1994 to date. This in effect has duplicated some of the serial numbers. Now I have one employee with two records, one record has an end date of null and the other is an end date in 1994. The 1994 end date is incorrect, because the employee also has a end date of null which means, he is still working. I need to find these records and delete the 1994 record. This needs to be done for approximately 59,000 records.

    The VB command that we wrote is:

    Option Compare Database
    Option Explicit

    Private Sub Command0_Click()
    On Error GoTo Err_Command0_click

    Dim db As Database
    Set db = CurrentDb()

    Dim rs As Recordset

    Set rs = db.OpenRecordset("select*from employeedatabase")

    Dim intserial As Long

    While Not rs.EOF
    intserial = rs("serial")

    If intserial = rs("serial") Then
    If rs("last_work") = Not "NULL" Then
    rs.Edit
    rs.Delete
    rs.Update
    rs.MoveNext
    End If
    End If

    Wend
    Beep


    Exit_command0_click:
    Exit Sub

    Err_Command0_click:
    MsgBox Err.Description
    Resume Exit_command0_click

    End Sub

    Private Sub Form_Click()

    End Sub
    ------------------------------------------------------

    However, when it is run, a "Type mismatch" message appears.

    HELP!!! <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  2. #2
    sabrantley0623
    Guest

    Re: VB Program

    It would seem to indicate that one of your fields is not set to the correct data type (i.e., integer).

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Program

    I am not an Access VB programmer, so it would take me quite a while to do the code that does what you want to do. You didn't say which statement the error was on, but I do see two things that are pretty obviously incorrect in your code and one of them could cause that error. First, you have the following two lines of code:

    <pre>intserial = rs("serial")

    If intserial = rs("serial") Then
    </pre>


    The first statement sets intserial equal to rs("serial") and the second compares intserial to rs("serial"). Since intserial was just set equal to rs("serial"), that comparison should always be equal. I think that what you want to do here is base your record set on a query that sorts the records on serial number, and then compare the serial number to the serial number from the next record.

    Second (this is the one that could cause that error), you have the following statement:

    <pre>If rs("last_work") = Not "NULL" Then
    </pre>


    From my Excel VBA programming, that looks like the wrong syntax. I think you need something more like the following, but I don't know the exact syntax for Access VBA:

    <pre>If Not IsNull(rs("last_work")) Then
    </pre>

    Legare Coleman

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: VB Program

    Hi Nick,
    In addition to Legare's points, your code will I think get stuck in an endless loop if it comes to a record where the last_work field is null because your MoveNext is included in the If...End If statement. This means that if last_work is null, you don't move to the next record and you're then stuck in your While...Wend loop.
    You also don't need the Edit and Update statements if you're deleting. Just the rs.Delete is fine. However, as Legare pointed out, you're comparing the serial number to the serial number of the same record so this code would delete all records where last_work is not null.
    I think the following code will do what you're after (it's untested though and it ignores the possibility of duplicate records where none of the last_work dates are null):
    Sub DeleteDups()
    Dim db As DAO.Database, rst As DAO.Recordset, rstTemp As DAO.Recordset
    Set db = CurrentDb
    Set rst = db.OpenRecordset("SELECT Serial, Count(Serial) AS SerCount FROM employeedatabase GROUP BY Serial;")
    rst.Filter = "[SerCount] > 1"
    While Not rst.EOF
    Set rstTemp = db.OpenRecordset("SELECT last_work from employeedatabase where serial = " & rst!serial)
    With rstTemp
    If Not IsNull(!last_work) Then
    .Delete
    End If
    .MoveNext
    End With
    Set rstTemp = Nothing
    rst.MoveNext
    Wend
    Set rst = Nothing
    Set db = Nothing

    End Sub
    Hope that helps.
    PS Backup your database before you try that code as it's Friday night and I'm on my way out the door as I write this!
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    ravey
    Guest

    Re: VB Program

    Several others have suggested ways to make your code work, but my question is why you want to do this in code in the first place. Are you going to have to do this over and over again? I think you could accomplish what you want very simply with a Delete Query, using "Is Null" as the criterion under the termination date field. Or maybe I just don't understand what you're attempting to do.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Program

    He wants to delete the record where the date is NOT null, and then only if there is another record in the table with the same serial number. I don't think you can meet the second condition using a query.
    Legare Coleman

  7. #7
    ravey
    Guest

    Re: VB Program

    Oops. I missed that. Should've read his original problem more carefully.

  8. #8
    New Lounger
    Join Date
    Jun 2001
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Program

    Nick,

    Please reassure us that you're working with a copy of your table and not the real thing! With the deletes built into your code, a misstep could be disastrous

    You've only specified one field [serial] by name, so the data-type mismatch has to between [serial] and variable 'intserial'. Suspect that if your changed your code from: Dim intserial As Long -to-- Dim intserial As Integer, that problem would probably go away, but it's something you need to check by comparing the data-types of the field versus variable.

    You realize, I hope, that by doing this deletion you are going to eliminate any history for any employee who was employed, departed, and then was reemployed. Thus, you'll end up with only the most recent period of employment, without any indication that the employee might have had 20 years of prior employment.

    Think the first thing I'd want to do is use the Find Duplicates query wizard so that I could see the actual gravity of the problem The resulting query is non-destructive (no deletes). You may or may not be surprised by the results. Your action plan could change once you've seen what you're dealing with. This problem can be resolved via query, but you need to be absolutely sure of the desired end result before embarking on a deletion process.

    If you want to zip and email a copy of your table, I'll be happy to take a look at it.

    Bob
    raskew@centurytel.net

  9. #9
    Lounger
    Join Date
    Jul 2002
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Program

    Thank You all for your input. I can assure you that I have made a copy of this database prior to starting any deletes. With that said, I would also like to add that this procedure is part of a nominal file, therefore there will be no loss of work history because all this information is stored in a master file.

    I've tried all the suggestions put forth, however none of them have been successful. The program suggested last week was very appreciated however, the program is not accepting certain commands such as, the rst statement (could it be a rs statement instead), nor is it accepting the deletedups statement or the filter.

    I have verified my data types. There are some text fields. Could that be the problem?

    It was suggested that I send a copy of the database, however, since the information is very confidential, I will have to get approval (I would probably modify it to take out the names to conserve confidentiality).

    Thanks again.

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VB Program

    You'll have to explain what you mean by the program "not accepting" things like rst. That is merely an object variable, and the only reason you should get an error is if you declared your recordset variable as rs and then pasted in code using rst. Where did you paste the deletedups routine and where are you trying to call it from? If either of the fields you're referencing--serial or last_worked--is a text field, then you have to be sure to include quote delimiters around the literal value in the code or query.

    Try this query. I would recommend you change it to a select query first to see if you actually are getting the right records, since I'm guessing at the fields and their exact relationships. This query basically tells Access to delete all those records with a last_worked of 1994 where there is more than one record for the same serial value. Keep in mind that if last_worked is a date rather than a number then you would have to change this to Year(last_worked)=1994 and if it is a string, you would have to put the 1994 in quotes.

    DELETE DISTINCTROW * FROM employeedatabase WHERE employeedatabase.last_worked = 1994 and ((select count(*) from employeedata AS E where E.serial = employeedatabase.serial))=2;
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •