Simple database...need help

[Wulf13]

I have a dilema. I'm working on a simple database for work that tracks the following Info:

Index Number (combination of letters and numbers)
Name
Organization
Date Issued
Is it suspended (yes/no format)
Date suspended

Every year this table needs to be re-accomplished by taking those records who are suspended and deleting all information in the fields except the Index field. Upon completing this action, I need it to take the information from the next record (minus the index number) and move it up. In other words if 01A01 belonged to BOB but it is suspended than I need BILL who had 01A02 to move up and take 01A01 and so on....

I need to find someway to accomplish the above function but can't figure it out. Please help me with this....

Ben

 

[ByteMyzer]

If the Index Number were an index field also used in a relationship with one or more other tables, this would not be a recommended course of action; it would compromise your database's referential integrity.

However, if the database is as you describe, working with but a single table, then this is entirely feasible.

The following code example accomplishes this for a table named MyTable, with the field names as you gave them ([Index Number], [Name], [Organization], [Date Issued], [Is it suspended], [Date suspended]):

Public Sub tblReIndex()

    Dim rst As Recordset
    ReDim Index(0) As String

    Set rst = CurrentDb.OpenRecordset("SELECT [Index Number] FROM MyTable " _
        & "ORDER BY [Index Number];")

    Do While Not rst.EOF
        Index(UBound(Index)) = rst![Index Number]
        ReDim Preserve Index(UBound(Index) + 1)
        rst.MoveNext
    Loop

    rst.Close

    CurrentDb.Execute "DELETE * FROM MyTable WHERE [Is it suspended]=Yes;"

    Set rst = CurrentDb.OpenRecordset("SELECT [Index Number] FROM MyTable " _
        & "ORDER BY [Index Number];")

    Do While Not rst.EOF
        rst.Edit
        rst![Index Number] = Index(rst.AbsolutePosition)
        rst.Update
        rst.MoveNext
    Loop

    rst.Close

End Sub

See if this works for you.

 

[Wulf13]

Compiler error..

Method or data member not found is the error message I received. By the way where did you learn VB? Since taking a course on Access, I've wanted to learn VB because I believe it would make my Access usage that more efficient. Thank you for your help

 

[ByteMyzer]

You did not specify which line of code was highlighted when you received this error message. If you can tell me that, I can help you determine what happened.

 

[Wulf13]

Do While Not rst.EOF
rst.Edit (this line here was highlighted)
rst![Index Number] = Index(rst.AbsolutePosition)
rst.Update
rst.MoveNext
Loop

 

[Carnafex]

Check that you have the reference for the data objects in your project. Do this by going into the VB code, then go
Tools -> References
Then check that Microsoft DAO 3.6 Object Library is selected. If it isnt, scroll down the list and find it.
That should fix the problem.

And to learn VB search around the web. There are a few good tutorials on learning VB out there somewhere.
Also check all the sample databases and stuff in these forums, they usually have some good examples of code ( but usually its a bit more complex ).

Ta

Jason

 

[Wulf13]

Jason,

I tried your suggestion and it didn't work. In regards to learning VB I was looking for something along the lines of a book that I can use a reference/learning tool almost like a text book. Thanks for your help though it is much appreciated.

 

[Wulf13]

This is just a bump

 

[Carnafex]

Hmm, dont know any any real good books that you can use to learn. I was sorta chucked into the deep end when I started my job. My boss just said 'do this' and so I learnt access. Talk about a crash course.

Check here first:
Recommended Reading Thread
and also here:
Recommended Reading Post

Perhaps they will help.

Could you also say what the error msg you got was apart from just showing the line of code? From the code sample you posted it *seems* okay.

Jason

 

[Russyfoot]

Good tutorial site

I've been learning access & code at http://www.functionx.com/access/index.htm.
It's great & you can download sample databases & work through them.

 

[Wulf13]

Ok the error message I get when I run the code within the builder is as follows:

Compile error:

Method or data member not found


The following line of the code is highlighted

Public Sub tblReIndex()

Can someone please help me? I don't know much about VB but am trying to learn.

 

[Wulf13]

Help...anybody....please....

 

[The_Doc_Man]

Usually this is a references error. You don't say which version of Access you are using so the advice given you about the specific version of the DAO library might be incorrect.

Go to any code page in a module, general or class makes no difference.

On the menu bar, click path Tools >> References

You get a complex dialog box. One part of the box is a scrolling list of names that look like file names in some cases. Probably a few of the names have checkmarks in the boxes next to them. One or more of the checked boxes includes "MISSING" (all caps!) in front of the name. This is your culprit. Uncheck the box. Now check it again. Close and re-open the references dialog box. If "MISSING" is no longer there, you are finished.

If the reference is still MISSING, click the browse button and look for a file with a name similar to the one on the MISSING line. Could be DAOxx.TLB where xx is 35 or 36 - or if AC97, could also be DAO2535 (the so-called "compatibility" DAO library). Or it might not be DAO, that's just the most common one. Kinda depends on exactly how Access was installed, as part of Office Professional or separately as an add-on to an Office Standard kit. Plus, could also be affected by a subsequent upgrade of another product for which you allowed the installer to delete files replaced by the new installation.

I'll also make note of the fact that you have a subroutine with a prefix that makes it look (to a READER, not the computer) like a table. If that's a typo, OK. If that's real, it is bad programming practice.

 

[Wulf13]

What do you mean by this:

I'll also make note of the fact that you have a subroutine with a prefix that makes it look (to a READER, not the computer) like a table. If that's a typo, OK. If that's real, it is bad programming practice.

It does refer to a table. I'm trying to setup a database that tracks information on one table and at the end of the year I can do a one button update.

 

[ByteMyzer]

I have an idea. Instead of wasting everyone's time trying to help with only have the information they need, how about attaching a copy of your database with what you have so far so that we can see a more COMPLETE picture?

 

[Wulf13]

Here is attached file

Here it is, didn't realize I was wasting people's time, sorry for that. I thought I was giving all the info that I had. Guess I'm not as smart as others.

 

[Wulf13]

Gratouitous bump