VBA for Acces getting End of Statement error in Immedate Window

[erickatd]

Should I code the form and not my access query?

[erickatd]

How can I make either one of the forms come up at a time? right now both forms come up when I click after update even thought I enter a where clause in the macro

[erickatd]

I wrote the following code to put in a macro but it keeps asking me to create another macro

If ([NCPDP_ID] = [NCPDP_ID]) Then
DoCmd.OpenForm ("Update Existing Credendtials")
Else
IF([NCPDP_ID] <> [NCPDP_ID]) Then
DoCmd.OpenForm ("Enter New Credentials")
End If

then I wrote this in expression builder but it's not recognizing the forms:

=IIf([NCPDP_ID]=[NCPDP_ID],[Forms]![Update Existing Credentials])
=IIf([NCPDP_ID]<>[NCPDP_ID],[Forms]![Enter New Credentials])

Expression builder will not except DoCmd.OpenForm

[DataMiser]

Should I code the form and not my access query?

Code like this goes in modules not in queries

[DataMiser]

Unless access uses IIF differently than what I am familiar with those lines are wrong

IIF(Expression,TruePart,FalsePart)

If you are just testing for a condition then you would use IF not IIF

also as I said before if you want to test data on the form against data in the data base then you have to specify the control which holds the data. Your code seems to point to the form but not the text box on the form and would therefore raise an error

[erickatd]

Unless access uses IIF differently than what I am familiar with those lines are wrong

IIF(Expression,TruePart,FalsePart)

If you are just testing for a condition then you would use IF not IIF

also as I said before if you want to test data on the form against data in the data base then you have to specify the control which holds the data. Your code seems to point to the form but not the text box on the form and would therefore raise an error

thanks very much

[erickatd]

I put code in module but it keeps prompting me to create a macro how do i stop this

[erickatd]

Oh the IIF, expression builder put that in autimatically

[Arnoutdv]

I put code in module but it keeps prompting me to create a macro how do i stop this

Maybe this can be of any help:
https://support.office.com/en-us/art...ad=US&fromAR=1

[erickatd]

Okay set warning is already set to off so where else would I look, it has to be some setting

[DataMiser]

How are you trying to execute the code?
If memory serves you must do so either with a macro or through an event such as a button click.

[erickatd]

I created the macro like the instruction said and names it AutoExec

[erickatd]

I don't know if autoexec is not running because my database is not trusted but then how do I make it trusted

[erickatd]

Oh it prompts me to create a macro every time I select run does that make a difference

[DataMiser]

when I get this working how can I get code to stay in Immediate window so I can have a button working because when I run this code not in immediate window I keeps prompting me to create a macro so the web said run this code in immediate window but I need this function in a button or textbox

You should not be doing it in the immediate window. Code goes in modules.
You have been told this more than once already. If you continue to try and do it the way you seem to be doing it then it is not ever going to give you the results you want.

[DataMiser]

Option Compare Database

Public Function TestMe() As Boolean
    MsgBox "this is a test"
    DoCmd.OpenForm "form1"
End Function

I created a module and added the above code
I then created a macro set it to runcode and pointed it to the TestMe function I just created
I also have a form named form1

When I execute the macro as expected it shows the message box then opens the form

[erickatd]

I don't think AutoExec is running so I can't execute modules yet, how do I get my database as trusted. The directions to get the message bar so I can enable the database isn't working.

[erickatd]

I made the database trusted and ran the autoexec and still have the module prompting me to create a macro when I select run

[DataMiser]

It may help if you were to show the code you put in the module.

Create a macro to run the code, save the macro then run the macro and it should execute your code. If it does not then you probably did something wrong but what I can't say as I have no idea what you are really doing at this point.

[erickatd]

I ran both codes and both prompt to create macro:

If ([NCPDP_ID] = [NCPDP_ID]) Then
DoCmd.OpenForm ("Update Existing Credendtials")
Else
IF([NCPDP_ID] <> [NCPDP_ID]) Then
DoCmd.OpenForm ("Enter New Credentials")
End If

[erickatd]

and this prompts me to create a macro as well:

If IsNull(Nz(DLookup("[NCPDP_ID]", "Main_Credential_Entry_Table", "[NCPDP_ID] =" & [Forms]![Enter New Credentials])& ""),"")) Then
DoCmd.OpenForm ("Enter New Credentials")
Else
DoCmd.OpenForm ("Update Existing Credentials")
End If
End Sub

[erickatd]

So now there is autoexec with setwarning on set to No and the database is trusted and in client setting in unchecked

[erickatd]

As soon I select run the module prompts me to create another macro instead of running my code

[erickatd]

Do you know why I am being prompted to create a macro when I select run every time I select run?

[DataMiser]

Do you know why I am being prompted to create a macro when I select run every time I select run?

I can't say as I have not saw any complete code
Did you try the example I gave? What you have posted is not a complete routine and I do not know if you are just posting it in part or if you are actually missing required elements.

I am also not familiar with this NZ() nor DLookup(). I do not know if those may be some internal function of access, a part of another module or just totally invalid.

[erickatd]

When I click run It prompts to create macro its has to be my settings

[erickatd]

Option Compare Database
Private Sub Mustlook()

If IsNull(Nz(DLookup("[NCPDP_ID]", "Main_Credential_Entry_Table", "[NCPDP_ID] =" & [Forms]![Enter New Credentials])& ""),"")) Then
DoCmd.OpenForm ("Enter New Credentials")
Else
DoCmd.OpenForm ("Update Existing Credentials")
End If
End Sub

and


Option Compare Database

Private Sub [cant2]()
If ([NCPDP_ID] = [NCPDP_ID]) Then
DoCmd.OpenForm ("Update Existing Credendtials")
Else
If ([NCPDP_ID] <> [NCPDP_ID]) Then
DoCmd.OpenForm ("Enter New Credentials")
End If
End Sub

[erickatd]

DLookup is the function for looking in a field and pulling back a record

[erickatd]

Nz handles the Null values

[erickatd]

When I pasted your code with form1, my database prompted me to create a macro as well, it has to be my settings or something

[DataMiser]

Where did you paste it?
Did you place it in a module?
Did you create a macro?
Did you try to run the macro?

I detailed the full procedure in that post and it should work as described

[erickatd]

I pasted in a Module and created a macro to run the module with form1 created and it prompted me to create a macro

[erickatd]

Every thing I code on my machine prompts me to create a macro, it has to be setting or something

[erickatd]

Can we compare setting tomorrow morning to see why my machine is doing this?

[DataMiser]

I pasted in a Module and created a macro to run the module with form1 created and it prompted me to create a macro

Did you save then run the macro?
Is that marco configured to runcode and pointed to the function I showed?

If the answers to the above are yes then I have no idea what you are experiencing. I had not created a macro in access in over 15 years and it still only took a few minutes to figure it out.

[erickatd]

actually runcode wouldn't work it set run open vusualbasicmodulemodule, it doesn't recognize the code under runcode

[erickatd]

I get an error every time I try to runcode:

Microsoft Access cannot find the name you entered in the expression, you may have specified a control object without specifying the current form or report

[erickatd]

the eror said set the control source to the form I did this and it still doesn't run

[erickatd]

error

[erickatd]

it has to be my machine the code works in immediate window but not in module controlled my macro