Passing A Table (or Form) Value Into A Function

[ballbearing]

This function in StartUp.mdb will compact and repair a defined database. Actually it does a great job too, btw. Anyway..here's my question: Since Access prefers to look at 'MyDocuments' all the time, some users place databases into different drives, directories, etc., I have a form which they can locate and select the database. The resulting path is then displayed in the form's textbox "txtOpenFile". This value is also placed into a table called "FilePath" for future reference. Clear as mud, right? What I'm having trouble with is figuring out how to place either the form's vale or (preferably) the table's value into the function below:

Function CompactDatabaseX()

Dim stappname As String

'Delete the last temporary compacted database
If Dir(&quot;compacted.mdb&quot <> &quot;&quot; Then
Kill &quot;compacted.mdb&quot;
End If

'Compact database.
DBEngine.CompactDatabase &quot;[form1!textopenfile]&quot;, &quot;textopenfile&quot;

'Rename database back to &quot;original.mdb&quot;
FileCopy &quot;compacted.mdb&quot;, &quot;Newtlc.mdb&quot;

'Launch newly compacted database
stappname = &quot;MSAccess.exe &quot; & &quot;Newtlc.mdb&quot;
Call Shell(stappname, 1)

'Close the StartUp Database
DoCmd.Quit
End Function


I need a nudge in the right direction.
Thanks!

Gawd I love this Access stuff..too bad I don't know what I'm doing.

 

[MakeItSo]

If Form1 is open then it's:

DBEngine.CompactDatabase Forms![form1!textopenfile],&quot;compacted.mdb&quot;

Else you need the DLookup-function:

Dim myFile as String
myFile=DLookup(&quot;textopenfile&quot;, &quot;user table&quot;, &quot;User='&quot; & currentuser

Hope this helps,
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[ballbearing]

Good Lord! Been sitting in front of this monitor too long, I guess. I pasted the wrong function above. DUH!
Maybe this one would be more appropriate:


Function CompactDatabaseX()

Dim stappname As String

'Delete the last temporary compacted database
If Dir(&quot;compacted.mdb&quot <> &quot;&quot; Then
Kill &quot;compacted.mdb&quot;
End If

'Compact database.
DBEngine.CompactDatabase &quot;Newtlc.mdb&quot;, &quot;compacted.mdb&quot;

'Rename database back to &quot;original.mdb&quot;
FileCopy &quot;compacted.mdb&quot;, &quot;Newtlc.mdb&quot;

'Launch compacted database
stappname = &quot;MSAccess.exe &quot; & &quot;Newtlc.mdb&quot;
Call Shell(stappname, 1)

'Close the StartUp Database
DoCmd.Quit
End Function


Thanks again!

Gawd I love this Access stuff..too bad I don't know what I'm doing.