problems creating a recordset on form load 1

[aperture]

hello all

i am having diffculty opening a recordset i generated from calculated query results. the code that appears below compiles, but when i try to run it, or open the form that it is it attached to, i get an error that says that one of the fields in the query cannot be found. the field both exists and is spelled correctly, which the error box asks me to check....the missing field is used in the join clause of the query code. the error may be there, but i cant catch it.

i am baffled. if someone can help, i would be very thankful.


Option Compare Database
Option Explicit


Dim dbCurrent As Database
Dim rsTemp As Recordset
Dim strSQL As String



Private Sub Form_Load()
Set dbCurrent = CurrentDb
strSQL = "SELECT AmtPaid FROM payfwdcalc;"
Set rsTemp = dbCurrent.OpenRecordset(strSQL)
With rsTemp
End With
End Sub



SELECT DISTINCT [Claimants Table].[Social Security #], [Claims Table].[Claim Number], [Payments Table].FeeSchedule, [Payments Table_1].Deductible, copaycalc.CoPayment, (([Payments Table].[FeeSchedule])-([Payments Table_1].[Deductible]))-(copaycalc.CoPayment) AS AmountPaid
FROM (([Claims Table] INNER JOIN [Claimants Table] ON [Claims Table].[Claim Number] = [Claimants Table].[Claim Number]) INNER JOIN ([Payments Table] INNER JOIN [Payments Table] AS [Payments Table_1] ON [Payments Table].FeeSchedule = [Payments Table_1].FeeSchedule) ON [Claims Table].[Claim Number] = [Payments Table].ClaimNumber) INNER JOIN copaycalc ON [Payments Table_1].FeeSchedule = copaycalc.FeeSchedule
GROUP BY [Claimants Table].[Social Security #], [Claims Table].[Claim Number], [Payments Table].FeeSchedule, [Payments Table_1].Deductible, copaycalc.CoPayment;



happy weeknd all!

aperture

 

[lameid]

I'm assuming that the literal SQL is in the query "payfwdcalc" and that query runs through the database window ok. I don't see anything wrong, so unless I'm overlooking something in the SQL then likely there is a hidden character in either the query or VB code giving you fits... My recommendation, zoom on the aliased field in the query design view and backspace over everything from just right of the colon and push it a few extra times for good measure. Retype the alias in the query.

In the VB code, try retyping the line
strSQL = "SELECT AmtPaid FROM payfwdcalc;"
directly below itself and delete the original. You might try a few other lines too if that doesn't help.

I don't think it should matter but you could put the declarations inside the procedure...

Option Compare Database
Option Explicit

Private Sub Form_Load()
Dim dbCurrent As Database
Dim rsTemp As Recordset
Dim strSQL As String

Set dbCurrent = CurrentDb
strSQL = "SELECT AmtPaid FROM payfwdcalc;"
Set rsTemp = dbCurrent.OpenRecordset(strSQL)
With rsTemp
End With
End Sub



Only other thought I have is why do you need to open a recordset on the form's on load event?

 

[Databaseguy]

strSQL = "SELECT AmountPaid FROM payfwdcalc;"