Replies

Oops… Sorry Donna… Didn’t know you had already answered Linda’s question… lol

Have a great night!
Alexya

Hi Linda…

First of all, I am assuming that you are using Next and Previous command button controls for navigation on this form right?… If that is not the case let me know…The solution I use to this error message is dealt with in my error trapping code… (I’ve taken out all the extra code, extra error traps, etc… but this is essentially what you need… I believe…)
________________________________________

Private Sub cmdNext_Click()
On Error GoTo cmdNext_Err

DoCmd.GoToRecord , , acNext

Exit Sub

cmdNext_Err:

Select Case Err.Number
Case 2105
Call MsgBox(“You have reached the end of the selected records… “, vbExclamation, “No Further Records”)
DoCmd.GoToRecord , , acLast
Resume Next
End Select
End Sub
_______________________________________

Private Sub cmdPrevious_Click()
On Error GoTo cmdPrevious_Err

DoCmd.GoToRecord , , acPrevious

Exit Sub

cmdPrevious_Err:

Select Case Err.Number
Case 2105
Call MsgBox(“You have reached the beginning of the selected records… “, vbExclamation, “No Previous Records”)
DoCmd.GoToRecord , , acFirst
Resume Next
End Select
End Sub
_______________________________________

All this code is doing is allowing the error to occur… and then dealing with the error by sending it to the error trapping code… which will then send a message to the user to let them know they are at EOF or BOF…. and once they click ok on the msgbox… it’ll go back to the form at the last or first record accordingly….

I hope this helps you out… …Personally, I can’t believe I am actually replying to a post!!! Oh well, if I’m steering you wrong, the wonderful experts on here will set me straight if I am, I’m sure…

TTFN
Alexya

Thanks a million Jeremy!! …The examples are terrific! …
You’re the best! Have a terrific day!!!

Thanks Jeremy! I had no idea that I could use the # sign in an expression like that! …Now that I know how to find numeric digits, is there a symbol I could use to find an alphanumeric character (a-z or A-Z)? …You’d think I would have read about this somewhere in the MANY books I have used for reference the past four months! … Actually, come to think of it, I did see something about that in the formatting or input mask sections… but I didn’t connect it to this… Thanks again!

Thanks Charlotte… I didn’t use IsDate because the field itself is Text format in the table… I thought it had to be Date/Time data type to use IsDate… Thanks for your help!

Just to let everyone know… I figured out a way to do this editing… I should have thought of this before!

Maturity: Trim(IIf((Mid([Mat],3,1)=”/” And Mid([Mat],6,1)=”/”),[Mat],””))

Thanks to everyone who thought about it… and sorry for wasting your time…

Have a terrific day all!

Hi… Sorry I’m so late in replying…but I had quite a few alterations to make first…

I had also done the same tests that you did… with the same result…

You were right about using the trim function… but I had to take it a little further… The problem was that I was using Make Table queries to create the final tables to be exported… and Access was using the default 255 length for most of the text fields… I had read in the Help files that Access doesn’t reserve the extra space for fields with this length, so I thought size wouldn’t have been an issue… I guess it definitely does reserve the space when exporting to dbf files… I changed my process to use an append query instead for each table… This way I define the proper field lengths and types, and it worked perfectly… I exported them and they were MUCH smaller!

Thanks again for your help!

Thanks Douglas! I will definitely do that for the main application!… I need all the help I can get as far as Securing the database files… I am leaving this position in a month and I am so afraid something will happen to the three applications I have created for them… They have been running soooo smoothly!!

Thanks again!

Boy Preston!! You are good!!! Thanks!

I had searched around in another database file that was working as I wanted this one… and was looking for the way they made it work… but I had no idea it would be the NAME of the macro! lol… I had seen the AUTOEXEC name…but just thought that was just a name that made sense… That’s cool!!! Thank you so much!

I just tried it and it works PERFECTLY!! …That was so quick!

I hate to ask silly questions…but I can only imagine the amount of time I would have wasted trying to figure this out on my own!!!

Have a great day!

Thanks Preston… I thought of that earlier… and it works… The only problem is that the underlying table is made through a Make Table query… I guess I could always just run a Delete query daily and just make an Append query so that it has up-to-date data every day… That should work right?

I’ll try it… Thanks again!
Have a great day!!

Works perfectly Tony… Thanks a million!!

Good Morning Charlotte!

Geezzzz…you are a wealth of knowledge! Thanks so much…

I am not running the database that I am trying to compact… I read that the db must be closed while being compacted… (I’m learning! )

I have access to the network drive I’m refering to…
but as for setting up permissions on that drive… I don’t know?… I thought permission were set up for users/groups… not different drives?

I tried refering to my C: drive (copied the files over to there, just for that purpose) and still got the error…

I haven’t tried to run it with shorter names… I’ll do that today…

Ummmm… I probably should know…but what’s a UNC path? …I read in the book I have for reference (Access 97 Programming Unleashed *which I might add, sucks!!!*) that it’s “Universal Naming Convention”… but it shows examples of UNC addresses as being “D:MSOffice97OfficeSamplesNorthwind.MDB” and “D:MSOffice97Test.XLS” … What is the difference between the mapped drive address I used… and the UNC path?

Good Morning Charlotte…

Thanks for the thought… I’ll try that… Will I need to do that for each database file I am going to try and put in the table for automated compacting? …I am assuming so… but thought I should check.. just in case you only meant that I should do it to the Compact.mdb file itself… Thanks again…

Good Morning Bart…
I understand everything you just replied… I’m going to ask some simple questions that have come to mind, in case the problem is something simple I’m missing…

I just created a new database file and wrote the following code in a module…

Option Compare Database
Option Explicit

Sub Compact()

Dim DBfile As String
Dim NewDBfile As String

DBfile = “H:MyfilesAccessDvdAccruals.mdb”
NewDBfile = “H:MyfilesAccessDvdAccrualsBU.mdb”
DBEngine.CompactDatabase DBfile, NewDBfile

End Sub

I compiled it, ran it and got the same “Invalid Argument” error…

1) Does the NewDBfile need to be already existing when I run this?… or does it create a new file with that name?

2) If I got the code to run successfully, would I see the NewDBfile name in Explore?

3) Do you think the problem could be anything to do with the network drive I’m refering to in the path?… I do have access to it, but it’s just a thought…

4) Could it be that I should be defining a workspace or something?

I’m sorry if these are silly questions but it’s “grasping at straws” time! LOL

Thanks again… Have a great day!

Hi Brian… Yep… It’s checked… I thought of checking that a while back… Thanks for the idea though… ….Re-installing Access huh?… I’m hoping not to do that… (I wouldn’t mind if I could do it myself, but I’d have to get the desktop support people up here (they keep the CD’s darn it!)… Try explaining to them that we need to uninstall and reinstall…. Not fun! ) …I’ll do it as a last resort…

Hello Bart… I tried your code (Thank you very much by the way! ) …I was actually doing the same thing!… **from a table that contains the paths to the mdb files**… Your code compiled fine, ran, but there was no change in the size of the files… so I stepped through it and it went to the error handler right after the DBEngine.CompactDatabase line, for each record… What does ” Kon niet gecomprimeerd worden.” mean? … Just curious…

I’m going with this code not working!…Just that one line… (the most important!)… No matter what I do, simple or complex to the other parts of the process…it’s fine EXCEPT that one line!… DBEngine.CompactDatabase blah,blah,blah….

(Sorry…had to vent for a second there!)
I’ll figure it out…Eventually…

Any more suggestions or ideas would be greatly appreciated! Have a great night everyone!