Forum Discussion
SEARCH DATA IN TABLE MS ACCESS USING VBA IN ACCESS FORM AND BUTTON
Hello
I have a table Tb_ACCOUNTS am trying to create a form with a search button and i have written the VBA code below but am not getting any out put. what could be the challenge with my code.
Table
Form
Code:
O
Option Compare Database
Private Sub SearchButton_Click()
Dim rst As DAO.Recordset
Dim strsql As String
strsql = "Select FORACID,ACCT_NAME,SCHM_CODE,STAFF_PF From Tb_ACCOUNTS Where FORACID= " & Tx_Search_Acct.Value & ""
Set rst = CurrentDb.OpenRecordset(strsql)
If rst.EOF Then
MsgBox " No data found: Check Account open date"
Tx_Acct_Num.Value = Nothing
Tx_Acct_Name.Value = Nothing
Tx_Sch_code.Value = Nothing
Tx_PFNum.Value = Nothing
Else
Tx_Acct_Num.Value = rst.Fields("FORACID")
Tx_Acct_Name.Value = rst.Fields("ACCT_NAME")
Tx_Sch_code.Value = rst.Fields("SCHM_CODE")
Tx_PFNum.Value = Fields("STAFF_PF")
End If
rst.Close
Set rst = Nothing
End Sub
6 Replies
KINENE_JAMIL As Arnel pointed out, the problem is that your search field, FORACID, is a text field, not a number. You can tell that from the screenshot because it is left aligned, whereas the number field next to it, ACCT_SOL_II, is right aligned.
Numbers do not need delimiters, text strings do need delimiters.
- change your strSQL to:
strsql = "Select FORACID,ACCT_NAME,SCHM_CODE,STAFF_PF From Tb_ACCOUNTS Where FORACID= '" & Tx_Search_Acct.Value & "'"- I have changed it, but still same problem no output.
Thank you.- Put a Debug.Print command in the line after you assign a value to the SQL string and before you open the recordset. Copy that SQL into a new query. What does it produce?
strsql = "Select FORACID,ACCT_NAME,SCHM_CODE,STAFF_PF From Tb_ACCOUNTS Where FORACID= " & Tx_Search_Acct.Value & ""
Debug.Print strsql
Set rst = CurrentDb.OpenRecordset(strsql)"
