SEARCH DATA IN TABLE MS ACCESS USING VBA IN ACCESS FORM AND BUTTON

[KINENE_JAMIL]

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

[#2]

change your strSQL to:

strsql = "Select FORACID,ACCT_NAME,SCHM_CODE,STAFF_PF From Tb_ACCOUNTS Where FORACID= '" & Tx_Search_Acct.Value & "'"

[#3]

@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.

[#4]

@George_Hepworth 

I have changed it to number, but still same problem no output.

Thank you.

[#5]

I have changed it, but still same problem no output.

Thank you.

[#6]

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)"

[#7]

@George_Hepworth 

Thank you. 

it helped me locate the error.  i had missed the Tx_PFNum.Value = rst.Fields("STAFF_PF").

Now its working.