Mapping of Data From Various Columns

paul31Hampshire · June 1st, 2006, 05:48 AM

I have an employee with an NI Code of A

NI Contribution thresholds are in the format:

A1a A1b A1c A1d

1000 100 10.00 50.00

However there are many NI Codes and many columns of thresholds for each Code. I am trying to write a query to say:

If NI Code = x then give me values from columns x1a x1b x1c x1d etc

Can anyone help?

Thanks

echovue · June 1st, 2006, 08:44 AM

Hmmm... I am not sure if this will help, but it could work.

Code:

Dim NICode as string
Dim rs as ADODB.Recordset
Dim cn as ADODB.Recordset
Dim strSQL as String

set cn = Application.CurrentProject.Connection
set rs = new ADODB.Recordset
NICode = "A"

strSQL = "SELECT " & NICode & "1a, " & NICode & "1b, " & NICode & "1c, " & NICode & "1d FROM theTableName"

rs.Open strSQL, cn

If Not(rs.EOF and rs.BOF) Then
   MsgBox rs.Fields(NICode & "1a")
   MsgBox rs.Fields(NICode & "1b")
   MsgBox rs.Fields(NICode & "1c")
   MsgBox rs.Fields(NICode & "1d")
End If

rs.Close
cn.Close

Set rs=Nothing
Set cn=Nothing

Hope that helps

Mike

Mike
EchoVue.com

paul31Hampshire · June 1st, 2006, 09:53 AM

How can I run this from a select query?

paul31Hampshire · June 1st, 2006, 10:05 AM

When running the code I receive the error: Compile Error: Invalid Outside Procedure

echovue · June 1st, 2006, 10:15 AM

Are you very familiar with VBA? If not, it's not too difficult and we could probably get through this.

What you probably want is a form with a text box (we'll call it txtLetter) and a button (we'll call this cmdRun)

Next pull up the properties for the button and go to the events tab. Choose On Click, and then hit the button with ... Choose the bottom option - it says something about code.

You should have the start and end of a Sub Procedure. Paste the code from above between those two line.
Next, change NICode="A" to NICode = txtLetter

Save it, run your form and you should get the 4 values popping up in Message boxes after you choose a number and click the button.

Mike

Mike
EchoVue.com

paul31Hampshire · June 1st, 2006, 10:46 AM

If I run from form or as a macro I receive the error:

Compile Error: Invalid Outside Procedure

echovue · June 1st, 2006, 10:49 AM

Usually you only get that error if your code is not enclosed by Sub and End Sub statements.

You shoul have...

Private Sub cmdRun_Click()

Code here

End Sub

The only thing that should be outside of that is any statement starting with Option ...

Mike
EchoVue.com

paul31Hampshire · June 1st, 2006, 10:53 AM

This is the format of my code:

Private Sub txtLetter_Click()
Dim NICode As String
Dim rs As ADODB.Recordset
Dim cn As ADODB.Recordset
Dim strSQL As String

Set cn = Application.CurrentProject.Connection
Set rs = New ADODB.Recordset
NICode = "txtLetter"

strSQL = "SELECT " & NICode & "1a, " & NICode & "1b, " & NICode & "1c, " & NICode & "1d FROM WorkPlace NI Breakdown"

rs.Open strSQL, cn

If Not (rs.EOF And rs.BOF) Then
   MsgBox rs.Fields(NICode & "1a")
   MsgBox rs.Fields(NICode & "1b")
   MsgBox rs.Fields(NICode & "1c")
   MsgBox rs.Fields(NICode & "1d")
End If

rs.Close
cn.Close

Set rs = Nothing
Set cn = Nothing

End Sub

When prompted with this error the word Set in row: Set cn = Application.CurrentProject.Connection is highlighted

echovue · June 1st, 2006, 10:59 AM

My bad!! Change

Dim cn As ADODB.Recordset

to

Dim cn as ADODB.Connection

Sorry!!!

Mike
EchoVue.com

paul31Hampshire · June 1st, 2006, 11:13 AM

hmmm still receiving same error