SQL in VB

[Wayne311]

I am using this code:

Private Sub Command47_Click()
Dim strCaseNumber As String
Dim strSQL As String
strCaseNumber = Forms(Me.Parent.Name).Controls("RcptCRDistrict Subform1").Form!Text48



strSQL = "SELECT RcptCRDistrict.[RcptNumber] " & _
"FROM RcptCRDistrict " & _
"WHERE ((RcptCRDistrict.[CRNumber])= " & strCaseNumber & ");"
DoCmd.RunSQL strSQL

End Sub

And I get this error (see attached)

What the heck?

Thanks
Wayne

[ajetrumpet]

try this change first:

"WHERE ((RcptCRDistrict.[CRNumber]) = '" & strCaseNumber & "');"

and if no luck there, try this:

DoCmd.RunSQL(strSQL)

and then try both at the same time

[Wayne311]

OK...gotta love those single and double quotes. But it didn't work. I have the "DoCmd.RunSQL strSQL" in other code and it works just fine. I can run the SQL statement in Access as a query and it works also. The variable "strCaseNumber" is also working...

Next?
Wayne

[ajetrumpet]

if that stuff didn't work Wayne, then the way I see it you have 2 options:

1) fix the corruption
2) check for a reference problem

that's not normal behavior, as you've probably already gathered, so corruption is a high possibility at this point.

the only work around I know for that is to import every object you have into a brand new db file. That will immediately tell you if the corruption was part of the db file itself. If that doesn't do it, what it tells you is that the corruption is inside one of the db objects.

I would doubt that though, if the rest works fine. I'm out of ideas with all of the above already said.

[Wayne311]

Adam,

I think I found the problem: "Action commands in Visual Basic can only carry out actions. This includes queries, or SQL statements that represent queries. The reason you are getting the error is probably because the SQL statement you are dealing with does not constitute an action.

Action queries are: UPDATE, DELETE, APPEND, and MAKE TABLE. These are the only ones that can be used in Visual Basic with the Docmd.RunSQL and CurrentDB.Execute commands."

With that in mind, can you point me in another direction? I am trying to create a search on a subform that, with user input, goes to the form record containing the found user input in the subform...got all that?

Wayne

[jgelpi16]

With that in mind, can you point me in another direction? I am trying to create a search on a subform that, with user input, goes to the form record containing the found user input in the subform...got all that?

Are you wanting to go the the record and still have all other records "visible" or "Available"? OR are you wanting the user input to show just the record they have "selected" (via filter)?

[ajetrumpet]

Adam,

I think I found the problem: "[I]Action commands in Visual Basic can only carry out actions.

OK, I truly feel like a moron.

I'm really sorry I didn't spot that, Wayne. Maybe too many cocktails yesterday during the super duper bowl??

and yes...I've got it all. Actually, quite a long time ago I helped someone else with the same issue and I did figure out how to do it. See post number 23 (the last one, either up or down to you) here: http://www.access-programmers.co.uk/...light=setfocus

I think you can use other docmd commands like "findrecord" and such when doing that too. The big thing was setting focus to the objects first, which sample code is there.

[Wayne311]

OK, I truly feel like a moron.

Hey welcome to my world. Don't beat yourself up over it...that is what a dominatrix is for!

I found a solution that seems too easy. I set up a combo box with the field in the subform to be searched by the user. It then shows the corresponding needed field in the form. The user can then just use the search at the bottom of the form to navigate there.

How is this going to come back and bite me when the subform table fills up with lots of records? Or am I just paranoid?

Wayne

[ajetrumpet]

users are gonna search record by record using the nav buttons?

hmmm...after 10 records I would even get annoyed.

[Wayne311]

Actually they type in the case number and the drop down gives them the receipt number.

Wayne

[ajetrumpet]

Actually they type in the case number and the drop down gives them the receipt number.

Wayne

that doesn't mean anything to me. so what? they see the number, THEN have to scroll by way of buttons...yes?

[Wayne311]

I don't see any need for scrolling...see attached.

Wayne

[ajetrumpet]

oh I see. no, that should be fine, if you're using the form's search bar. that's part of access and I've never seen it used, but if your users don't mind, I'd do it.