asked on

Code not releasing Table

Hi all,
The following code is from a converted Macro, which seems to be missing one step. When I run the command from a button, the table opens and all the records are selected and deleted and the table [disappears] when its supposed to close. If I use CTRL+F6 the now empty Table is still open. But the focus is back on the Form where its supposed to be. If I hit the command button again I get an error message.
If someone can point out the error I would appreciate it

Jim Horn

define 'following code'.

Paulo357

ASKER

Sorry doing to many things at once.
-----------------------------------------------------------------------------
Function ClearTbleQ1_ClearTbleQ1()
On Error GoTo ClearTbleQ1_ClearTbleQ1_Err

DoCmd.SelectObject acTable, "Q1", True
' Open Table
DoCmd.OpenTable "q1", acViewNormal, acEdit
' Selects all records
DoCmd.RunCommand acCmdSelectAllRecords
' Deletes records
DoCmd.RunCommand acCmdDeleteRecord
' Closes Table
DoCmd.RunCommand acCmdClose
' Opens Form
DoCmd.OpenForm "Update Inv details and produce Log", acNormal, "", "", acEdit, acNormal
DoCmd.RepaintObject acForm, "Update Inv details and produce Log"

ClearTbleQ1_ClearTbleQ1_Exit:
Exit Function

ClearTbleQ1_ClearTbleQ1_Err:
MsgBox Error$
Resume ClearTbleQ1_ClearTbleQ1_Exit

End Function

9swampy

I suggest you'd want to use something more along the lines of the following...

Function ClearTbleQ1_ClearTbleQ1()
On Error GoTo ClearTbleQ1_ClearTbleQ1_Err
docmd.setwarnings false
docmd.runsql "Delete * from Q1"
docmd.setwarnings true
DoCmd.OpenForm "Update Inv details and produce Log", acNormal, "", "", acEdit, acNormal
ClearTbleQ1_ClearTbleQ1_Exit:
Exit Function

ClearTbleQ1_ClearTbleQ1_Err:
MsgBox Error$
Resume ClearTbleQ1_ClearTbleQ1_Exit

End Function

Paulo357

ASKER

Cheers Swampy
Are we placing this behind an Event Procedure?
I assumed we were and hit several errors,
firstly, The systems said it was a Private Sub and all the assioated issues related to the different wording of subs and function. I tinked a bit but think I am on the wrong track

Sorry ...novice

ASKER CERTIFIED SOLUTION

9swampy

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Paulo357

ASKER

Cheers Swampy
That worked fine. I am aware my use / code is 'brutish' as I am entirly self taught so I have been able to tackle doing things in stages and only receintly discovered examples of combining steps. even this code you assisted me with here is much more elegant than mine. Just wondering why your final code differed from the first? And I am still fuzzy on Function and Sub
Cheers P

9swampy

Use a function when you want to return a single result, use a subroutine when you don't. "Don't" includes when you need to return multiple results, but return them as modified parameters of the sub rather than as a return result of a function.

I was being lazy first time ;-)

Paulo357

ASKER

Thanks for the support, It still reads like a foreign language to me, but more and more bits are slowly making sense now
I read "modified parameters" as changable results and 'Result' fixed
Right?
What I dont get is were [how] one adds a Function, all the Buttons on my Pages have Private sub behind them

Paul

9swampy

private sub cmdButton_Click()
dim g1 as string
dim g2 as string
dim RESULT_gOutput as string
dim RESULT_qBothSame as boolean

g1 = "Hello "
g2 = "World"

msgbox Combine(g1,g2)
CombineSub(g1,g2,RESULT_gOutput,RESULT_qBothSame)
if RESULT_qBothSame then
msgbox RESULT_gOutput & vbcrlf & "Both strings were the same"
else
msgbox RESULT_gOutput & vbcrlf & "The two strings were different"
end if
end sub

private function CombineFunction(p_g1 as string, p_g2 as string) as string
CombineFunction = p_g1 & p_g2
end function

private sub CombineSub(p_g1 as string, p_g2 as string, RET_g3 as string,RET_qBothSame as boolean)
RET_g3 =p_g1 & p_g2
RET_qBothSame =(p_g1=p_g2)
end sub