Embedded SQL issue with Combo update

[Fatti]

Public Sub cboDate_AfterUpdate()

Dim strSQL As String
Dim intShootID As Integer

  intShootID = Me.cboDate.Value

strSQL = "SELECT tblCompetition.[Shoot_ID], tblCompetition.[Shooter_ID], " & _
            "tblCompetition.[Calibre_ID], tblCompetition.[Class_ID], tblCompetition.[Projectile_Weight], " & _
            "tblCompetition.[Round_1_Score], tblCompetition.[Round_2_Score], tblCompetition.Round_3_Score, " & _
            "tblCompetition.[Round_4_Score], tblCompetition.[Round_5_Score], tblCompetition.[Round_6_Score], " & _
            "tblCompetition.[Competition_Total] " & _
         "FROM tblCompetition " & _
         "WHERE tblCompetition.Shoot_ID = " & intShootID & ";"

Me.RecordSource = strSQL

End Sub

With this code im getting the following error:



It hightlights the Me.RecordSource = strSQl line but I cant figure it out. Where is the mismatch? Both sides of the equation are strings?

Am I missing something?

One thing I did notice was in the local variable watcher the sql statement was incomplete as if the variable length wasnt long enough. Could this be an issue or is that more related to the size of the field in the Variable watcher?

[aytee111]

Is cboDate an integer? Sounds like a date.

[Fatti]

File is located below.

wetsphere.com/images/PistolDB.zip

cboDate is an integer as you will see in the code. I am only referencing the ID column. Perhaps this is an issue though but the variable watcher seems to be pulling back the correct values.

I no longer get the data mismatch error but it still doesn't populate the datasheet.

Any assistance would be much appreciated.

[Fatti]

Sorry. The form you want is frmCompDS.

[aytee111]

Does the sql string work in a regular query (if you copied it to query design and ran it stand-alone)?

You may be missing the Me.Requery statement after you have changed the recordsource.

[Fatti]

Yeah the SQL is fine.

I was under the impression that changing the record source property would trigger a requery anyway. Either way I added one in to no effect.

[Fatti]

I find that when I use the drop down at the top of the frmCompDS form to select a date then it actually creates a record instead of returning a record.

Any idea why this is or how to stop it?

http://wetsphere.com/images/PistolDB.zip

[aytee111]

Sorry for the delay - are you still having problems?

[Fatti]

No I ended up just creating a new data sheet form with a filter in the header. Not sure it's the best way to do what I wanted though. Thanks anyway.