Make Table with WHERE clause - HELP!

[ijergas]

Hello everyone,

I am racking my brains with this one:

I am creating a query/table with the following code:

Private Sub Command32_Click()

Dim strSQL As String
Dim var As String

strSQL = "SELECT [doctoremail].RESOURCE, [doctoremail].MRN INTO [Tbl_AttachPatients] FROM [doctoremail] WHERE [doctoremail].RESOURCE = 8861509"

DoCmd.RunSQL strSQL

End Sub

Notice that there is a "WHERE" clause that specifies which records I want queried. The problem is that I need the user to be able to specify which ones need to be queried. Currently I have a combo box from which they choose a "RESOURCE" number.

I guess my question is, how to I get this code to point to a field on a form (the combo box) so that it queries the records specified by the user?

Any help you can provide would be greatly appreciated.

Thank you,

Josh

 

[Full_Williams]

try


Private Sub Command32_Click()

Dim strSQL As String
Dim var As String
Dim int as integer

int = Me.Combobox ' the box that the user uses to select the resource number

strSQL = "SELECT [doctoremail].RESOURCE, [doctoremail].MRN INTO [Tbl_AttachPatients] FROM [doctoremail] WHERE [doctoremail].RESOURCE = int"

DoCmd.RunSQL strSQL

End Sub

If this doesn't work you might need to set it up as a parameter query.

Rob

 

[ijergas]

Hi Rob,

Thank you for your suggestion, I had actually already tried that and it did not work. Can you please explain to me what a "parameter query" is?

Thanks,

Josh

 

[Full_Williams]

Josh,
Double check to make sure the bound column of the combo box is set to 2 or whatever column the Resource number is.
Rob

 

[ijergas]

Well, here is my code now:

Private Sub Command32_Click()

Dim strSQL As String
Dim var As String
Dim Myint As Integer

Myint = Forms![emailopenonedoctor]![Combo52].Value ' the box that the user uses to select the resource number

strSQL = "SELECT [doctoremail].RESOURCE, [doctoremail].MRN INTO [Tbl_AttachPatients] FROM [doctoremail] WHERE [doctoremail].RESOURCE = Myint"

DoCmd.RunSQL strSQL

But, now I get error called "Runtime Error '6'" and it says "overflow". I made sure that my combo box bound column is set correctly.

Any more thoughts?

Thanks,

Josh

 

[ijergas]

Now, after trying to make Myint a String, i get the message "Invalid Column Name 'Myint'"

 

[DataMiner]

I think Access is probably reading "myint" as text.
Instead, try

Myint = Forms![emailopenonedoctor]![Combo52].Value ' the box that the user uses to select the resource number

strSQL = "SELECT [doctoremail].RESOURCE, [doctoremail].MRN INTO [Tbl_AttachPatients] FROM [doctoremail] WHERE [doctoremail].RESOURCE = " & Myint

 

[ijergas]

That was it!! Thank you thank you thank you...

Josh