You have been so helpful - thank you!!
But, I'm getting:
User-Defined Type Not Defined
= Dim dbcurrent As CurrentDb
The Help is telling me to use the TYPE Statement in the module - but I
cannot figure out how to add a type and make this defined????
Thanks
:
Aha, if strLinkCriteria is a number, then it is a syntax problem. The single
quotes need to be removed so SQL knows it is a number. Also see below for
correct Dim statement:
Private Sub Command1_Click()
Dim stDocName As String
Dim stLinkCriteria As String
Dim rstemp As Recordset
Dim dbcurrent as Database
strLinkCriteria = InputBox("Please Enter the ICN Number")
If Len(Trim(strLinkCriteria)) = 0 Then
DoCmd.OpenForm "f_Menu"
Else
strLinkCriteria = "[icnno] = " & strLinkCriteria
Set dbcurrent = CurrentDb
Set rstemp = CurrentDb.OpenRecordset("select * from t_Entry where " _
& stLinkCriteria & ";")
If rstemp.RecordCount <= 0 Then
MsgBox "There is no record for this ICN Number."
rstemp.Close
Set rstemp = Nothing
Set dbcurrent = Nothing
Exit Sub
End If
End If
:
Spelling is correct, it is a number, looking at strLinkCriteria - makes me
think Am I missing a DIM example DIM dbcurrent As Database. So I tried that
and it tells me that is undefined - so how do I define it (Type statement in
the Module)....????
:
puzzlement. Is icnno spelled correctly? Is it text? step through in debug
to see what strLinkCriteria is before you do the openrecordset. Also I did a
typo, but it will not affect anything. I notice you created an object
dbcurrent, and I, in haste, type it as currentdb. You might want to change
it.
Let me know what happens
:
Some clean up??? If I could rewrite code that quick I'd be the VP of IT !!!
The Compiler is saying Sytax error in where clause: "Set rstemp...where"
Suggestions???
Private Sub Command0_Click()
Dim stDocName As String
Dim stLinkCriteria As String
Dim rstemp As Recordset
strLinkCriteria = InputBox("Please Enter the ICN Number")
If Len(Trim(strLinkCriteria)) = 0 Then
DoCmd.OPENFORM "f_Menu"
Else
strLinkCriteria = "[icnno] = '" & strLinkCriteria & "'"
Set dbcurrent = CurrentDb
Set rstemp = CurrentDb.OpenRecordset("select * from t_Entry where " _
& stLinkCriteria & ";")
If rstemp.RecordCount <= 0 Then
MsgBox "There is no record for this ICN Number."
rstemp.Close
Set rstemp = Nothing
Set dbcurrent = Nothing
Exit Sub
End If
End If
End Sub
:
I made a few changes here (once i got over the giggles
(not really, I never laugh at anyone, your code is not bad, but needs some
clean up)
As there is not Cancel in a Click event, we can tell whether a user clicked
cancel or just closed the input box with no entery. Instead of a cancel
event, we can use that value to determine whether to continue.
Private Sub Command1_Click()
Dim stDocName As String
Dim stLinkCriteria As String
Dim rstemp As Recordset
strLinkCriteria = InputBox("Please Enter the ICN Number")
If Len(Trim(strLinkCriteria)) = 0 Then
DoCmd.OpenForm "f_Menu"
Else
strLinkCriteria = "[icnno] = '" & strLinkCriteria & "'"
Set dbcurrent = CurrentDb
Set rstemp = CurrentDb.OpenRecordset("select * from t_Entry where " _
& stLinkCriteria & ";")
If rstemp.RecordCount <= 0 Then
MsgBox "There is no record for this ICN Number."
rstemp.Close
Set rstemp = Nothing
Set dbcurrent = Nothing
Exit Sub
End If
End If
:
Don't laugh, this SQL has taken me a long time..
I'm trying to:
A. Pop Up with "Please Enter the ICN Number"
click ok and it takes you to that record..
B. If CANCEL is clicked it takes you to a form.
C. If no number is entered, or number does not exist a MsgBox "There is no
record for this ICN Number." appears.
If you run it - I think you will see what I'm trying to do:
Private Sub Command1_Click()
Dim stDocName As String
Dim stLinkCriteria As String
Dim rstemp As Recordset
stLinkCriteria = "[icnno]=" & "'" & InputBox("Please Enter the ICN
Number") & "'"
Set dbcurrent = CurrentDb
Set rstemp = CurrentDb.OpenRecordset("select * from t_Entry where " &
stLinkCriteria & ";")
If Cancel = True Then
DoCmd.OpenForm "f_Menu"
End If
If rstemp.RecordCount <= 0 Then
MsgBox "There is no record for this ICN Number."
Exit Sub
End If
End Sub
:
The click event has no cancel argument. How do you know you want to cancel?
Convert the macro code to VBA and post that.
:
I agree, it's just sometimes quicker to let Access write the VBA, then work
with it.
Obviously not in this case... The "If Cancel..." code you suggested doe not
work within the VBA (Before or after the String).
If you could help me convert this Macro-VBA to plan code not using a Macro,
I can start doing it that way all the time...
Thanks
Private Sub Command4_Click()
On Error GoTo Err_Command4_Click
Dim stDocName As String
stDocName = "m_EditAppeal"
DoCmd.RunMacro stDocName
If Cancel = True Then
DoCmd.OpenForm "f_Main"
End If
Exit_Command4_Click:
:
This is another example of why most seasoned developers don't use macros.
They are very limited.
I would suggest you convert your macro to VBA and put it in the click event
of the button. Then you can add this code to open your form. Where you
will put it depends on what else is happening in the code.
If Cancel = True Then
DoCmd.OpenForm "MyFormName"
End If
:
On my form I have a command button, it asks for information via a macro.
My question is, if Cancel is pressed I need it to open a form... I have ON
EXIT set to a macro to open a form (but form don't open).. I have Default and
Cancel both set to YES.... I wish there was a ON CANCEL command!!!!
Can you please help me open a from when Cancel is clicked?
Thanks
