• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 231
  • Last Modified:

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
0
Paulo357
Asked:
Paulo357
  • 4
  • 4
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
define 'following code'.
0
 
Paulo357Author Commented:
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
0
 
9swampyCommented:
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
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
Paulo357Author Commented:
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
0
 
9swampyCommented:
Yes, this is the [Event Procedure] your command button should point to, however the procedure header should be left as automatically created by clicking the ellipsis to the right of where you selected [Event Procedure]

Paste the following between the "Private Sub NameOfCommandButton_Click" and "End Sub" created for you

On Error GoTo EH
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * from Q1"
DoCmd.SetWarnings True
DoCmd.OpenForm "Update Inv details and produce Log", acNormal, , , acFormEdit, acWindowNormal
Exit Sub
EH:
  MsgBox Err.Number & vbCrLf & Err.Description

Once you've mastered doing things this way, you should be looking into using ADODB instead of DoCmd. DoCmd's a nasty way to do things, even if an easy starting point...
0
 
Paulo357Author Commented:
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
0
 
9swampyCommented:
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 ;-)
0
 
Paulo357Author Commented:
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
0
 
9swampyCommented:
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
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now