SQL to Select Form Values

[Lrn2Code]

Hello,

I'm about ready to rip my hair out. I've been trying to run a SQL statement to select all the values on a form where the checkbox value is true. The form is designed to run edit checks on entities (which I'm also having trouble with getting only one entity's records to be reviewed at a time, but that's another hair ripper...).

Anyway, I keep getting an error (3135 syntax error in JOIN operation) in my SQL statement (in blue). The code I'm trying to run follows -

Option Compare Database
Option Explicit

Dim strSQL As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As QueryDef

Private Sub EditCheck_AfterUpdate()

If Me.EditCheck = True Then (This line is running properly)

strSQL = "Select * from (Form_frmDataCheck.(frmMakeFloppySub)) where (Editcheck.Value) = true"

Debug.Print (strSQL)

Set rst = CurrentDb.OpenRecordset(strSQL) (This is the line it blows up on)

If rst.RecordCount > 0 Then
rst.MoveLast
rst.MoveFirst

If rst.RecordCount > 1 Then

MsgBox ("Select only one entity at a time for edit checking.")

If rst.RecordCount = 1 Then

strSQL1 = "Update c_orgs set editcheck = true where orgid = '" & Orgid & "'" (I put this in to update the c_orgs table value with what is on the form)
Debug.Print (strSQL1)

Set qdf = CurrentDb.CreateQueryDef("", strSQL1)
qdf.Execute

End If

End If

End If

End If



I have changed how I reference the form - using (), or "", or the ! between Forms and the form name (I get an error when I do that too) - trying to figure out the proper syntax. I have searched this site multiple times in the past week and either can't find what I'm looking for or don't really know what I'm looking for.

Is there some place that explains the proper syntax for referencing form values?

One other thing - when I check an entity on the form and go to run the edit check I get a write conflict error and can't figure out why. (Being a novice is such a pain.)

Any help/guidance you can provide would be much appreciated.

Thanks for your time.

 

[KenHigg]

Here's the 'teach a man to fish' answer: Look at the sql statement as you step through the code

 

[Lrn2Code]

Hi Ken!

I have been, my problem is that I don't know what the proper code syntax is supposed to be. How can I fix something if I don't know what it's supposed to look like?

Does a period go before the form name and it's field name? Do there have to be brackets around the field name? Is the entire form referenced supposed to be encased in parentheses?

I've tried multiple versions of periods, brackets, commas, exclamation points, parentheses, quotes, etc.

If I knew what the rules were that'd be a huge help. You know, like "i before e, except after c".

The error messages tend to be useless, in my humble opinion.

Thanks for your reply.

 

[KenHigg]

Post what your sql code looks like when you step through it.

 

[Lrn2Code]

This is what Debug.Print displays in the Immediate Window -

Select * from (Form_frmDataCheck.(frmMakeFloppySub)) where (Form_frmDataCheck.(frmMakeFloppySub.(Editcheck).Value) = true

I don't know why it looks like there is a space between V and a in value because there isn't one in the SQL code or in the immediate window.

Thanks.

 

[KenHigg]

Your control references should have already been resolved by the time your codes get this far. What you need to do is concatenate all these small bits of string values into one long string:

strSQL = "Select * from " & (Form_frmDataCheck.(frmMakeFloppySub)) & " where (Editcheck.Value) = true"


See if that works -
(I'm not sure what the Editcheck.Value is supposed to work?)

 

[Rabbie]

Try changing your code to this

[SIZE=2][SIZE=3][FONT=Arial Narrow][COLOR=#008000]Option Compare Database
Option Explicit

Dim strSQL As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As QueryDef

Private Sub EditCheck_AfterUpdate()

[/COLOR][COLOR=green]If Me.EditCheck = True Then [COLOR=black](This line is running properly)[/COLOR][/COLOR]

[/FONT][/SIZE][COLOR=seagreen][SIZE=3][FONT=Arial Narrow][COLOR=blue]strSQL = "Select * from "  & Form_frmDataCheck.frmMakeFloppySub & " where (Editcheck.Value) = true"

[/COLOR]Debug.Print (strSQL)

Set rst = CurrentDb.OpenRecordset(strSQL) [COLOR=red](This is the line it blows up on)[/COLOR]

If rst.RecordCount > 0 Then
  rst.MoveLast
  rst.MoveFirst

  If rst.RecordCount > 1 Then

    MsgBox ("Select only one entity at a time for edit checking.")

    If rst.RecordCount = 1 Then

      strSQL1 = "Update c_orgs set editcheck = true where orgid = '" & Orgid & "'" [COLOR=black](I put this in to update the c_orgs table value with what is on the form)[/COLOR]
     Debug.Print (strSQL1)

      Set qdf = CurrentDb.CreateQueryDef("", strSQL1)
      qdf.Execute

       End If

     End If

  End If

End If[/FONT][/SIZE]


[/COLOR] 
[/SIZE]

and see if that helps. The From part of a Select query needs to be a domain name not a field name

 

[Lrn2Code]

Thank you Rabbie!

The syntax error is gone but a method or data member not found error occurs on frmMakeFloppySub when I compile.

The editcheck is my check box and is within that subform, though so I would think it needs to be referenced.

Is there some other way I can reference that subform?

Or would something else be set up incorrectly to cause this?

 

[KenHigg]

I'll yield to Rab... Sorry I wasted our time

 

[Lrn2Code]

You didn't waste my time. I appreciate your assistance.

Hopefully there will be a day when all of this makes more sense.

Thank you!

 

[Rabbie]

Thank you Rabbie!

The syntax error is gone but a method or data member not found error occurs on frmMakeFloppySub when I compile.

The editcheck is my check box and is within that subform, though so I would think it needs to be referenced.

Is there some other way I can reference that subform?

Or would something else be set up incorrectly to cause this?

Here is a link to a tutorial on acceessing fields on a subform. Hope it helps

 

[Lrn2Code]

Thank you, Rabbie.

I ended up trying a different approach by populating the c_orgs table with any entities that had an edit check value of true then doing a record count to make sure only one value was selected before the error check is run.

I could not figure out why that code would not reference the form or why the frmMakeFloppySub wasn't listed as a member of the db. The properties box for that form didn't even have a control source field but a record source field instead. In any event, what I've done appears to be working at this time.

Thanks again for your guidance. I will save that web page for future reference.

Lrn2Code