problem with a small code

joe789

Registered User.
Local time
Today, 21:38
Joined
Mar 22, 2001
Messages
154
Hi Folks,

I have created a query named UniqueNPROV which lists all unique values for the NPROV field from the Claims table. I am trying to write some code, so that once a command button is clicked, a unique table is created from each one of the unique NPROV values and the table has the same name as the NPROV value it uses as the selection criteria. The code is pasted below, and it doesn't work, wondering if someone can tell me what is wrong with it.

Option Compare Database

Private Sub Command0_Click()

Dim SQL As String
Dim SelectCriteria As String
Dim FieldItem As Variant


Set db = CurrentDb

For Each FieldItem In UniqueNPROV
SelectCriteria = ItemData(FieldItem)

SQL = "Select Claims.* INTO " & SelectCriteria & "From Claims where n_prov = " & SelectCriteria & ""

Next FieldItem


End Sub


Any help would be greatly appreciated.

Thank you very much,

Joe
 
You are never executing the query:

Code:
Option Compare Database 

Private Sub Command0_Click() 

Dim SQL As String 
Dim SelectCriteria As String 
Dim FieldItem As Variant 


Set db = CurrentDb 

For Each FieldItem In UniqueNPROV 
SelectCriteria = ItemData(FieldItem) 

SQL = "Select Claims.* INTO " & SelectCriteria & "From Claims where n_prov = " & SelectCriteria & "" 

'You may also find that you will need single quotes (') around the SelectCriteria in the "Where" statement.
[i]'SQL = "Select Claims.* INTO " & SelectCriteria & "From Claims where n_prov = '" & SelectCriteria & "'"[/i]


[b]db.Execute SQL[/b]

Next FieldItem 


End Sub
 
Thank you -- How do I specify the fieldname

Thanks Travis. I tried the code, and it gave me an run-time error #13, but I think it is easy to fix. I think I am getting the error because it appears that I do not know the syntax to use to tell the code which field to look at in the query (or table if it is easier).

....

....

For Each FieldItem In NPROV
SelectCriteria = ItemData(FieldItem)

....

....

If the query is named NPROV and the one field the query returns with the unique values is named n_prov, what would be the syntax to use with the 'For Each FieldItem in ...' to specify what field in the query the SelectCriteria should examine.

Thank you for your help,

Joe
 
I thought you where going through a Combobox's RowSource.

You want to open a RecordSet and Loop through it. Look up RecordSets in the Help File for examples on how to open a Query as a recordset and loop through it.
 

Users who are viewing this thread

Back
Top Bottom