SQL Syntax

[Beemerang]

Hi All

I would like to populate form fields with records from a table/query so that they are bound to the recordsource and the record to be displayed can be selected from a combobox. I am hoping that in this way, any changes made are propagated to the original table.

I have created a combobox from which the primary key can be selected and added all the fields from the table appearing on this form as values.

In my Change event for the combobox I have this code:

Private Sub cboID_Change()
 Me.RecordSource = "SELECT b01_Participants.*FROM b01_Participants WHERE (((b01_Participants.ParticipantID)= " & cboID.Column(1) & "));"
    Me.Refresh
End Sub

However, I receive an 3075 error when this code runs which seems to relate to my syntax or an extra ")".

Any advice would be greatly appreciated.

Beem

 

[plog]

How do you determine if you have more M&M's in your left hand than your right hand?

 

[Beemerang]

How do you determine if you have more M&M's in your left hand than your right hand?

I'm smiling here, I have counted the parentheses but regardless of which combination I try, I still get an error, even if there are the same number on each side. What am I missing?

Beem

 

[namliam]

Beem, you are aware that a combobox is a zero-based array, right?
Thus that the first column is actually 0 not 1

Edit: And/or is your ID actually a number or a string/date?

 

[Beemerang]

Beem, you are aware that a combobox is a zero-based array, right?
Thus that the first column is actually 0 not 1

Edit: And/or is your ID actually a number or a string/date?

Hey Namliam,

Thanks for replying!

I am aware of that, thanks. The ID is a number, autoassigned and accessed from a dropdown.

Beem

 

[jdraw]

You need a space between *From

eg * From

 

[Beemerang]

You need a space between *From

eg * From

Thanks Jdraw, I've corrected that, don't know how I missed it! However, I still get the error about the parentheses.

 

[jdraw]

How about posting the latest SQL?

 

[Beemerang]

How about posting the latest SQL?

Here we go:

Private Sub cboID_Afterupdate()
MsgBox "SELECT b01_Participants.* FROM b01_Participants WHERE (((b01_Participants.ParticipantID)= " & cboID.Column(0) & "));"
 Me.RecordSource = "SELECT b01_Participants.* FROM b01_Participants WHERE (((b01_Participants.ParticipantID)= " & cboID.Column(1) & "));"
    Me.Refresh
End Sub

I added the msgbox function just to see if the string output showed anything funny.

Beem

 

[jdraw]

You have different values of cboId in your code sample.
column(0) and column(1) but that shouldn't affect the parentheses.

A tip:
make a variable and assign your sql string to it.
then use debug.print yourVariableName or
msgbox yourVariableName

will reduce copy/paste typing issues.

Still don't know why you're getting the error msg.

 

[Beemerang]

You have different values of cboId in your code sample.
column(0) and column(1) but that shouldn't affect the parentheses.

A tip:
make a variable and assign your sql string to it.
then use debug.print yourVariableName or
msgbox yourVariableName

will reduce copy/paste typing issues.

Still don't know why you're getting the error msg.

Thanks Jdraw, yep I fixed the one in the code and not in the msgbox!

I appreciate the attempt to help though.

 

[namliam]

MsgBox "..." & cboID.Column(0) & "));"
Me.RecordSource = "..." & cboID.Column(1) & "));"
You sure about the numbering though?

 

[namliam]

Failing that being a solution, add:
Debug.print YourSQL
To your code, take the generated SQL from the immediate window and post it in a query... See if it runs... failing that post the full sql here (remember to format and use code tags please)

 

[Beemerang]

MsgBox "..." & cboID.Column(0) & "));"
Me.RecordSource = "..." & cboID.Column(1) & "));"
You sure about the numbering though?

Thanks Namliam, you were right, I had accessed the second array element instead of the first!

The SQL code I actually copied and pasted from a query that works perfectly so I am really confused!

Thanks for helping me with this, I appreciate the effort.

Beem

 

[Beemerang]

Beem, you are aware that a combobox is a zero-based array, right?
Thus that the first column is actually 0 not 1

Edit: And/or is your ID actually a number or a string/date?

Namliam, you're a star!

The issue WAS with me accessing the incorrect array element, it seems to be working now.

Thanks a million!

Beem,