VBA App

[john-86]

Hello all, here's part of the code I've been working on. I was wondering if someone would mind having a quick look over the code and tell me if there were any serious logic/syntax errors that needed to be addressed before I try it out.

I'd love to try and explain the project to you... but it's so very complex, it'd take me most of the day to type it out. Basically I want to import data from one table ("GIS Import Table") into two tables ("PN Name" & "PN Place/Feature"). The lettersNGR() function tests two values within the "GIS Import table" to return results to the "PN Place/Feature" table.

I'm thinking of making this a module so that it can be run by a macro. Are there any special considerations I have to make because of this?


Option Compare Database
Option Explicit

Public db As Database
Public nameRS As Recordset
Public featRS As Recordset
Public tempRS As Recordset

Set db = CurrentDb()
Set nameRS = db.OpenRecordset("PN Name", dbOpenTable)
Set featRS = db.OpenRecordset("PN Place/Feature", dbOpenTable)
Set tempRS = db.OpenRecordset("GIS Import Table", dbOpenTable)
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''
Function lettersNGR()

Public eQualifier As Variant
Public nQualifier As Variant
Public letters As Variant
Dim east As Variant
Dim north As Variant

east = str (tempRS.Fields("Easting").Value) '''converts integer to string
north = str (tempRS.Fields("Northing").Value) '''converts integer to string

If ((east = "4?????") And (north = "10?????")) Then
letters = "HZ"
eQualifier = 400000
nQualifier = 1000000
ElseIf ((east = "4?????") And (north = "11?????")) Then
letters = "HU"
eQualifier = 400000
nQualifier = 1100000
ElseIf ((east = "4?????") And (north = "12?????")) Then
letters = "HP"
eQualifier = 400000
nQualifier = 1200000
ElseIf ((east = "3?????") And (north = "11?????")) Then
letters = "HT"
eQualifier = 300000
nQualifier = 1100000
End If

End Function
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''
Private Sub importFromGIS()

tempRS.MoveLast
tempRS.MoveFirst

While (Not tempRS.EOF)
lettersNGR '''call to function
nameRS.AddNew
featRS.AddNew
nameRS.Fields("Name Displayed As").Value = tempRS.Fields("featName").Value
featRS.Fields("NGR letters").Value = letters
featRS.Fields("NGR Easting").Value = (tempRS.Fields("Easting").Value - eQualifier)
featRS.Fields("NGR Northing").Value = (tempRS.Fields("Northing").Value - nQualifier)
nameRS.Update
featRS.Update
tempRS.MoveNext
Loop

End Sub



Thank you all very much for your help! When I learn to become a VBA expert I will sit on these forums, like you, trying to help wannabes like myself :)

Kind regards,
John

Me after a few weeks of VBA:
:wot - :dunno - :think: - :banghead: - :help

[Norie]

John

Do you actually need code for this?

Could you not use an update/append query?

[john-86]

Hi Norie,
pardon my lack of knowledge as I am fairly new to all this. I don't actually know if I do need code for all this. I'm just doing what I can with the rather limited knowledge I have. Could you tell me more about the method of using a update & append query?

Thanks,
John

[Norie]

John

Can you post/attach some sample data?

[john-86]

Hi Norie, just before I went to send some sample data I got the last piece of code to work. Although it may be a complex solution to a simple problem it works! I can always go back and revise my code at a date when I have a lil more time on my hands.

Having got all the functions working, I need now to integrate them all into a module so I can put the code in a macro and things can be done at the click of a button.

I've tried this and the first problem I've been presented with is a runtime error '13' - "Type mismatch".

I declare my database & recordset variables in the "declerations" part of the module. I tried initialising the variables (with the 'Set =' statement) just below (outside of a procedure) and it told me I wasn't allowed to do that. So I tried setting the variables within each of the three functions inside the module and I get this type mismatch error message. The line I use to initialise the recordset is below.

Set nameRS = db.OpenRecordset("PN Name", dbOpenTable)

Can anyone tell me where I'm going wrong?

Kind regards,
John

[Tommy]

Hi John,

It told you you are not allowed to run procedures outside of a sub or function. So to correct and at least get you going for now, I wrapped the sets into an Initialion routine.



Sub InitializeGlobalVar()
Set db = CurrentDb()
Set nameRS = db.OpenRecordset("PN Name", dbOpenTable)
Set featRS = db.OpenRecordset("PN Place/Feature", dbOpenTable)
Set tempRS = db.OpenRecordset("GIS Import Table", dbOpenTable)
End Sub


I normally use Access through VB so I could be wrong, the table names may need to be enclosed with "[PN Name]" because of the spaces. I don't use spaces in name, big PITA.

[Norie]

John

Perhaps you need to declare the recordset as either a DAO or ADO recordset.

Tommy

You only need the [] for queries I think.

[Tommy]

John,
east = "4?????" is not going to work, east actually has to equal "4?????" not "4". So if I am wrong just ignore me :) ROFL


Function lettersNGR()

Public eQualifier As Variant
Public nQualifier As Variant
Public letters As Variant
Dim east As Variant
Dim north As Variant

east = Str(tempRS.Fields("Easting").Value) '''converts integer to string
north = Str(tempRS.Fields("Northing").Value) '''converts integer to string

If Left(east, 1) = "4" And Left(north, 2) = "10" Then
letters = "HZ"
eQualifier = 400000
nQualifier = 1000000
ElseIf Left(east, 1) = "4" And Left(north, 2) = "11" Then
letters = "HU"
eQualifier = 400000
nQualifier = 1100000
ElseIf Left(east, 1) = "4" And Left(north, 2) = "12" Then
letters = "HP"
eQualifier = 400000
nQualifier = 1200000
ElseIf Left(east, 1) = "3" And Left(north, 2) = "11" Then
letters = "HT"
eQualifier = 300000
nQualifier = 1100000
End If
End Function


Norie,

LOL story of my life right thing wrong time/ right time wrong thing.

[john-86]

Hi all, I appreciate your help.

Tommy:
You were right, in a sense, it didn't work. The question marks represented wildcards meaning each '?' could be any number. I found out afterwards that the wildcard '#' was the correct one to use for numbers. Also I had to use 'Like' instead of '='. Having said that, thanks for the idea of the initialisation procedure! It didn't even occur to me! :friends:

Norie:
My references weren't set up correctly to begin with, and to be very honest I don't quite know what I'm looking for yet (I am, after all, only in my 3rd week of VBA training). What references do you suggest I have (what ones are 'must haves')?

:bow: Thanks guys:bow:
John

[john-86]

Hi all,
three other questions I forgot to ask.

1: What's the difference between a function and a procedure?

2: How do you return variables from a function?

3: How do you use the brackets at the side of the function name? I know in JavaScript that's where you put variable names as arguments to the function... same deal?

Thanks,
John

[john-86]

Sorry to make lots of posts without there being replies from other... I have no intention of "spamming" - although, imo, it could hardly be called that.

So, I had a play around with my references (increased the priorities of DAO 3.6) and I seem to be past that "type-mismatch" error... yay for me arbitrarily clicking on buttons in desperate hope for something to work!

However, as always, there seems to be another problem. Two to be precise...

Working with grid references I need to be able to include the number 0 at the beginning of an integer... normally, if there's a 0 preceeding the number it will disregard it.... is there any way to enforce that it's kept there?

I appear to have an infinite loop... I've tried a few different ways of doing this, but I can't find a working solution. When I use the following code, I get four entries (the tempRS Recordset has 4 entries, so that's good), but they're all the first entry in the import table. It's as if the tempRS.MoveNext line wasn't even there! The loop doesn't cycle through the records as it should...


Function importFromGIS()
Dim x As Variant
Dim y As Variant
initialiseGlobalVariables '''call to sub
x = tempRS.RecordCount
y = 0
tempRS.MoveLast
tempRS.MoveFirst
MsgBox (x)
Do
Do While (y < x)
formatFields '''call to function to format fields to no dp
lettersNGR '''call to function to determine NGR letters
nameRS.AddNew
featRS.AddNew
nameRS.Fields("Name Displayed As").Value = tempRS.Fields("featName").Value
featRS.Fields("NGR letters").Value = letters
featRS.Fields("NGR Easting").Value = (tempRS.Fields("Easting").Value - eQualifier)
featRS.Fields("NGR Northing").Value = (tempRS.Fields("Northing").Value - nQualifier)
nameRS.Update
featRS.Update
tempRS.MoveNext
y = y + 1
If tempRS.EOF Then
Exit Do
End If
Loop
Loop Until y < x

End Function



I can't begin to express my appreciation for your patience!:thumb

Kind regards,
John

[Norie]

John

Why not just use tempRS.EOF as the sentinel for the loop?

Function importFromGIS()
Dim x As Variant
Dim y As Variant
initialiseGlobalVariables '''call to sub

tempRS.MoveFirst
While Not (tempRS.EOF)
formatFields '''call to function to format fields to no dp
lettersNGR '''call to function to determine NGR letters
nameRS.AddNew
featRS.AddNew
nameRS.Fields("Name Displayed As").Value = tempRS.Fields("featName").Value
featRS.Fields("NGR letters").Value = letters
featRS.Fields("NGR Easting").Value = (tempRS.Fields("Easting").Value - eQualifier)
featRS.Fields("NGR Northing").Value = (tempRS.Fields("Northing").Value - nQualifier)
nameRS.Update
featRS.Update
tempRS.MoveNext
Wend

End Function
Here's an example of how a function can be passed parameters and return a value.

Function TestFnc(a As Long, b As Long) As Long
TestFnc = a+b
End Function

Sub Test()
Msgbox TestFnc(10,12)
End Sub

[john-86]

Hi Norie,
call me crazy, but I tried using the EOF as the sentinal with no joy. That's how I'd set up the loop structure of my first attempt, it just didn't seem to like it. I may have just made a silly mistake though. Take me a lil while to get used to it I guess.
Thanks for your quick demo on function parameters. It is just like JS, just wanted to make sure.

Thanks,
John