Select count data in access with textbox input value condition

Ngoclong226

New Member
Joined
Mar 20, 2019
Messages
5
Hi
I have a database access with “LAL data” table and “Job_no” field.
I have create a userform in excel with input textbox “txt_jobno”
How can i count the number of fields in access table with criteria as textbox input value.
My code of selecting the data in acess:
Const strSQL_c as string = “select count([LAL data].Job_no) as CountofJob_no From [LAL data] Where ([LAL data].Job_no = “”me.txt_jobno.value””)”
But it did not work.
Could anyone help?
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,518
Office Version
  1. 365
Platform
  1. Windows
See if this works better for you:
Is me.txt_jobno.value a variable's value?
If so, I don't think you want quotes around it, i.e.
Code:
[COLOR=#333333]Const strSQL_c as string = "select count([LAL data].Job_no) as CountofJob_no From [LAL data] Where ([LAL data].Job_no = me.txt_jobno.value)"[/COLOR]
 
Last edited:

Ngoclong226

New Member
Joined
Mar 20, 2019
Messages
5
Thanks for you help.
I try your solution but it happen error “ no value given for one or more required parameter”. Here is all the code:
Const strSQL_c As String = "SELECT Count([LAL data].Job_No) AS CountOfJob_No FROM [LAL data] WHERE ([LAL data].Job_No = me.Txt_JobNo.Value)"

Const strConnection_c As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Users\70F4908\Desktop\Programming document\CODE\Production data input project\Data.accdb;Mode=Read;Persist Security Info=False"

Dim cn As ADODB.Connection

Dim rs As ADODB.Recordset

Dim lngCount As Long

Set cn = New ADODB.Connection

cn.Open strConnection_c

Set rs = New ADODB.Recordset

rs.Open strSQL_c, cn, adOpenForwardOnly ‘(this line happen eror)

If Not rs.EOF Then

lngCount = rs.Fields(0).Value

End If
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,518
Office Version
  1. 365
Platform
  1. Windows
I try your solution but it happen error “ no value given for one or more required parameter”.
I believe that is telling you that it is not receiving any value from "me.Txt_JobNo.Value".

Where exactly have you placed this VBA code?
How is it being called/run?
Are you sure that you have the variable named correctly?

An easy way to check to see if you can get the value in that manner within that code is to place this line at the top of your code, and see what it returns:
MsgBox
me.Txt_JobNo.Value
 
Last edited:

Ngoclong226

New Member
Joined
Mar 20, 2019
Messages
5
Hi
I put the code in a command button in a simple input form (1 textbox, 1 button)
When i input a value in textbox then click button, i will return the how many jobno in the database match with the input data.
If i only put the code msgbox txt_jobno.value it still show the correct value i input to the textbox.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,550
Messages
5,529,472
Members
409,884
Latest member
Msinmath
Top