Question on Variables

[Trachr]

So this is probably a dumb question but Im having issues getting it set up right.

I will be using a certain code multiple times on various forms so Im trying to set it up to be as easy as possible to move it to a different form so Im using more variables then Im used to... that way I can edit a variable and have it affect the whole code easier.

My problems is with a form element... I want to be able to set a variable to a form element and have it functional.

So if I were to set say Form_Listbox = Me.publist how would I go about using that in the code to for instance.. Requery

Heres an example:

Dim Form_Listbox As String
Form_Listbox = Me.publistList

Me.publistList = Null
Me.publistList.Requery

So if I wanted to change the Me.publistList into a variable how would I change those 2 lines of code so it reads my variable correctly?

 

[Uncle Gizmo]

First Change This;-

Dim Form_Listbox As String
Form_Listbox = Me.publistList

Me.publistList = Null
Me.publistList.Requery

To This :-

Dim strListbox As String
strListbox = Me.publistList

Me.publistList = Null
Me.publistList.Requery

Otherwise it looks like you are creating a form variable "Confusing"

 

[Trachr]

Ok, but how would you integrate that variable into the last 2 lines of code, thats where Im having the most issues...

that and

If IsNull(Me.publistList) Then

it keeps popping up errors when I try in the case of the isnull it does a improper use of null error and the other 2 lines have various other errors.

Thanks

 

[Trachr]

ah never mind I figured it out... had to set it as a control not a string

 

[Trachr]

out of curiosity should I use the Set command for setting variables or does it not matter?

Heres my variable section, eveything is in working order but Im trying to make it as clean as possible as well since Ill be reusing a lot of this code in other forms.

    Dim StrRecordNameOrig As String
    Dim StrRecordNameNew As String
    Dim StrRecordIdent As String
    Dim StrRecordCapsChk As String
    Dim StrRecordSQL As String
    Dim StrRecordTbl As String
    Dim StrRecordTblID As String
    Dim StrRecordTblF1 As String
    Dim StrTextName As String
    Dim ctlListBox As Control
    
    Set ctlListBox = Forms!frmPublisherList!publistList
    
    StrTextName = "Publisher"

    StrRecordTbl = "tblpublishers"
    StrRecordTblID = "PublisherID_PK"
    StrRecordTblF1 = "PublisherName"
    
    If IsNull(ctlListBox) Then
        MsgBox "Please Select a Publisher"
    
    Else
        
        StrRecordNameOrig = ctlListBox
        StrRecordNameNew = InputBox("Enter Corrected " & StrTextName & " Name", "Add " & StrTextName & "", "" & StrRecordNameOrig & "")
        StrRecordCapsChk = StrComp("'" & StrRecordNameNew & "'", "'" & StrRecordNameOrig & "'", vbBinaryCompare)
        StrRecordIdent = DLookup("[" & StrRecordTblID & "]", "[" & StrRecordTbl & "]", "[" & StrRecordTblF1 & "]='" & StrRecordNameOrig & "'")
        StrRecordSQL = "Update " & StrRecordTbl & " SET " & StrRecordTblF1 & "='" & StrRecordNameNew & "' WHERE " & StrRecordTblID & "=" & StrRecordIdent & ""

So I guess Im asking ... did I do all this properly or am setting myself up for disaster eventually by doing something fundamentally wrong that I haven't noticed yet.

I know at one point someone mentioned I should be using explicits but Im still hazy on why that's an advantage other then it not letting you accidentally set a variable

Thanks

 

[Uncle Gizmo]

>>>should I use the Set command for setting variables<<<

You Dim Variables, thats String, Integer, Long, Date

You Set when it's an object as you have done Re:

    Dim ctlListBox As Control
    
    Set ctlListBox = Forms!frmPublisherList!publistList

 

[Trachr]

ok, good to know

Thanks

 

[Uncle Gizmo]

Your code looks OK...

Accept .... If IsNull(ctlListBox) Then

I haven't done this, or seen it done, so if it was me I'd run some tests to see what's happening..

 

[vbaInet]

Just my 2 cents to clarify some points made.

You use Let when you want to set the value of a variable, but this keyword is optional

I used to use "Let" all the time when I started out in MS Access.... I was told it was being retired, and I shouldn't use it. So I would advise against its use unless, until you get the real story from MS....

An interesting experiment... Does "Let" work in VB.Net? If it does the that would indicate to me that there are no plans to retire it.

 

[Trachr]

another thing Im unsure about, my StrRecordIdent variable is the primary key for a table, so its a number.

Defining it as a string has worked for me but would it be more efficient to define it as a integer? I guess Ive never seen a purpose to go beyond string really since it always works for me... having to make one of my variables a control has me second guessing some things.

 

[vbaInet]

Just my 2 cents to clarify some points made.

You use Let when you want to set the value of a variable, but this keyword is optional

I used to use "Let" all the time when I started out in MS Access.... I was told it was being retired, and I shouldn't use it. So I would advise against its use unless, until you get the real story from MS....

An interesting experiment... Does "Let" work in VB.Net? If it does the that would indicate to me that there are no plans to retire it.

I see you're using your Moderator privileges to edit my post Uncle Gizmo

Let doesn't work in VB.NET, I was only clarifying its use in VBA.

 

[vbaInet]

another thing Im unsure about, my StrRecordIdent variable is the primary key for a table, so its a number.

Defining it as a string has worked for me but would it be more efficient to define it as a integer? I guess Ive never seen a purpose to go beyond string really since it always works for me... having to make one of my variables a control has me second guessing some things.

Best to match the data type of your field definition to that of the variable.

 

[Trachr]

Ok, I altered my code to refect the integers and it all worked fine... last question... I know I can declare a variable global or public and it will effect everything, but is there a way to declare a variable specifically for a form? I use a few variables in every subroutine in my form and seems like it would be more efficient to declare the variables when the form loads and not worry about each subroutine.

I Wouldn't necessarily want the variables to go outside said form since other forms may be using similar or the same variables themselves... (I am recycling some of my code for multiple forms)

Is this possible?

 

[Uncle Gizmo]

would it be more efficient to define it as a integer?

I'd be careful an integer has a maximum of just under 33,000 To be on the safe side I'd go for Long (long integer)

 

[vbaInet]

I Wouldn't necessarily want the variables to go outside said form since other forms may be using similar or the same variables themselves... (I am recycling some of my code for multiple forms)

Is this possible?

Declare your variable at the very top, just below the Option Explicit keyword. I like to use Private instead of Dim when I declare variables at the top.

I hope you understand what section I'm referring to?

 

[vbaInet]

For example:

Option Explicit

Private myVariable As Long       <--- Declare it here
___________________________________________________________________

Private Sub MyControl()

End Sub

 

[Trachr]

right below where it says Option Compare Database?

I dont see an Option Explicit anywhere

Option Compare Database

Private Sub publistAdd_Click()

Thats what I have at the top

 

[vbaInet]

That's a good one to have. I'll advise you have it everywhere. It forces you to always declare your variables.

Option Compare Database
Option Explicit

Private myVariable As Long
----------------------------------------------------------------------


Private Sub publistAdd_Click()

 

[Trachr]

ok, Ill do that Thank you

 

[Trachr]

So I changed my top to this:

Option Compare Database
Option Explicit

Private ctlListBox As Control
Private StrTextName As String
Private StrRecordTblF1 As String
Private StrRecordTbl As String
Private StrRecordTblID As String

' Edit the Lines below

Set ctlListBox = Forms!frmCollectorList!FrmListBox       'Point this at ListBox
StrRecordTblF1 = "CollectorName"                         'Main Table Field your Editing
StrRecordTbl = "tblCollectorList"                        'Table your editing
StrTextName = "Collector"                                'Edit this to whatever Name you are editing
StrRecordTblID = "CollectorID_PK"                        'Table Primary ID Field Name

and now I get the error anytime I hit a button:
The expression ON Click you entered as the event property setting produced the following error: Invalid Outside Procedure

what did I do wrong lol