Run VBA code from Macro

[gheyman]

I have a Macro

If Isnull([Text123]And[Text125) Then
MessageBox
Blah Blah Blah...
Else
SetValue
Item=[ProgramListID]
Expression=[Text123]

RunCode
Function Name CreatePID


But I am getting an error message (Error Number 2766)

"This Object doesn't contain the Automation Object 'CreatePID'


I am assuming the problem is in the "RunCode" because the rest ran without problem before I added this section. I am trying to run the VBA code I have

Private Sub CreatePID()
'USED FOR CREATING A PID

    Dim padded As String
    Dim erg As Variant

    Select Case 5 - Len(CStr(PID_SequenceID))
        Case 4
            padded = "0000" & CStr(PID_SequenceID)
        Case 3
            padded = "000" & CStr(PID_SequenceID)
        Case 2
            padded = "00" & CStr(PID_SequenceID)
        Case 1
            padded = "0" & CStr(PID_SequenceID)
        Case Else
    
    End Select

    Me.PIDNumber = Me.ProposalIdentifier & padded & [RevisionId] & "-" & Me.ProgramListID.Column(2) & "-" & Me.JobTypeListID.Column(2) & "-" & Me.YearIdGenerated & Me.MonthIdGenerated

    erg = DLookup("RFPMgr_ID", "tblRFPManager", "PID_Number='" & Me.PIDNumber & "'")

    If erg > 0 Then
        MsgBox "This PID already exists. Please choose other parameters!", vbCritical, Application.Name
        Exit Sub
    Else
        SaveThisRecord
        CreateRFPManagerRecord
        MsgBox "New PID saved!", vbInformation, Application.Name
    End If

End Sub

 

[james_lankford]

I'm surprised this works
If Isnull([Text123]And[Text125) Then

I would think it would have to be

If Isnull([Text123]) And Isnull([Text125) Then

also,
Private Sub CreatePID()
is private
maybe that's why it can't see it --- I don't know


also
Select Case 5 - Len(CStr(PID_SequenceID))


I don't know if it makes a difference but what if PID_SequenceID is null or blank or more than 5


also you could eleiminate the whole case thng and just do


right("000000000" & cstr(PID_SequenceID), 9)


and why don't you jst get rid of the macro ?
is it really necessary ?
can't you just replace it with more VBA code
and put the sub it in the same place as Sub CreatePID()

 

[gheyman]

Background: This is creating a Number for a Proposal (Proposal ID = PID) based on several pieces that make the whole number.

The Isnull works as is. If I run just this part of the macro it works and initiates the message box.

I would prefer to get rid of the Macro, but still learning. (I did not write the VBA code)

I hadn't thought about it, but you're right, I really don't need the sequential numbering to be 5 characters long.

I don't know enough Access to know if the Private Sub is the problem



There will never be a blank because Its looking at a table where the PIDGen_ID is an Autonumber. It will never need more than 5 because this will never create 10,000 PID Numbers.

 

[Micron]

Not being a big macro user, I'll speculate that it won't work even if it's not declared as Private (although you are correct that the macro won't see it because it is) because this code is on a form. Form and report variables are not visible to any other form or report regardless of how you declare them, and neither are the procedures. So are they visible to a macro? That's the part I don't know, and I've already made my excuse for that

Edit:
The Isnull works as is. You tested with both being Null and it works? I don't get the same result. If both are null, or the second is not, or they both are not, my test message box doesn't appear. It only appears if the first isn't and the second is.

 

[welshgasman]

Did you create this via the macro builder?

I ask, as I seem to recall a macro cannot not run a Sub, which is what you have declared, but it has to be a Function.?
Change the declaration to a function and report back.

https://www.access-programmers.co.uk/forums/showthread.php?t=291402&highlight=macro

 

[gheyman]

Did you create this via the macro builder?

I ask, as I seem to recall a macro cannot not run a Sub, which is what you have declared, but it has to be a Function.?
Change the declaration to a function and report back.

https://www.access-programmers.co.uk/forums/showthread.php?t=291402&highlight=macro

After your comment I took out all the "padding". You're right I didn't need alll the 00000

I also did the concatenation in an unbound field and then did another SetValu. So it's all in a macro. I'd prefer to have it in VBA. But don't know how to do the equivalent of set value yet. I tried Me.PIDGen = Text95. Or Me.PIDGen = [text95]. But neither worked

 

[welshgasman]

Is not setvalue just

PIDGen = me.Text95 ?

Also I'd start using real descriptive names for your controls. You might know what Text95 is now, but later on, probably not. Why not call it txtPIDGen ?

 

[gheyman]

Thanks, I'll try that. I agree with naming convention. Text95 is an unbound text box. Once I get the functionality working I go back and rename.