Getting Access form field values in SQL string in the VBA

Question

I may be asking silly question as I am very new in ms access and VBA and stuck with this,

I am having a SQL string in my Access VBA as below example

select customer_id from customer_table where customer_name in ('Max','Jack')

I want user to add/replace the customer_name in the query using a ms access Unbound text box name as txtCusName. I tried to modify the SQL string as below but getting compilation error, advice me where I am doing the mistake?

select customer_id from customer_table where customer_name in ( & Me.txtCusName & )

Again, this is just example and I have bigger query for my project and which is working if I hardcoded the values but wondering how I can get from access form.

I totally agree, thanks for the advice and will be great if you could share the solution for this. — Chito
– Chito, Commented Nov 27, 2014 at 0:35
I could have used this one customer_name = " & "'" & Me.txtCusName & "'" if a single value but wondering how I can get the text in the SQL "IN" function, that is where I am lost. — Chito
– Chito, Commented Nov 27, 2014 at 0:38
Expecting a user to properly format the required string is simply asking for trouble and not user-friendly. You (and your users) would be better off if you populate a listbox with available values and construct what you need from the selected items. — AVG
– AVG, Commented Nov 27, 2014 at 0:59

Chito · Accepted Answer · 2014-12-01 20:27:36Z

1

I wrote like the below code to allow user to just type as 'Max','Jack' in form field to get the query filter and works.

select customer_id from customer_table where customer_name in ( " & "" & Me.txtCusName & ")"

answered Dec 1, 2014 at 20:27

Chito

3152 gold badges9 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Getting Access form field values in SQL string in the VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related