hiding a record in access query

[Rizvi]

Hello everyone
Need help on the following
I have a form which has a query displaying records on certain criteria. under this query there is a checkbox and once user click on this a message box takes users input yes / no. "yes" will perform the actions as per the code. but for "no" value i want the record which user selected should be hidden from this query. if any one can help on this.


thanks in advance

[ranman256]

the table should have a field [show] (as y/n) the default set to true/yes
the query would be
select * from table where [show]=true (aka yes,-1)

when the user sets it to NO , refresh the list so the record hides.
the [show] field afterupdate event

me.requery

[Rizvi]

the table should have a field [show] (as y/n) the default set to true/yes
the query would be
select * from table where [show]=true (aka yes,-1)

when the user sets it to NO , refresh the list so the record hides.
the [show] field afterupdate event

me.requery

Thanks ranman256 for the idea, i have now figured it out

[Rizvi]

hi ranman256, i am stuck, i have added field in my table for show record yes/no but the field is not updated when i am running the below code, if you can help me out, the field name is "showrecord"


Private Sub cmdaddnew_Click()
On Error GoTo cmdaddnew_Click_Err


On Error Resume Next




Dim db As Database
Dim rs As Recordset
Dim strEmail, strBody, userdept, authgroupemail, currenttime, crntusername, expr As String
Dim objOutlook As New Outlook.Application
Dim objEmail As Outlook.MailItem
Dim tdate As Date
Dim esqls As String


Set db = CurrentDb
Set rs = db.OpenRecordset("tbl_fxmain", dbOpenDynaset, dbSeeChanges)


tdate = Now()



rs.Edit
rs!Customer_CIF = Me.cbmcif
'txtcustname = rs!Customer_Name
'txtcstsegment = rs!Customer_Segment
'txtcustrm
rs!TXN_Amount = Me.TXN_Amount
rs!TXN_CCY = Me.TXN_CCY
rs!Value_Date = Me.Value_Date
rs!TXN_Reference = Me.TXN_Reference
rs!Debit_Account_CCY = Me.Debit_Account_CCY
rs!Debit_Account_Number = Me.Debit_Account_Number
rs!Reimbursing_Bank = Me.cmbreimbank
rs!Trade_Remarks = Me.Trade_Remarks
rs!Txn_DatenTime_Initiator = tdate
rs!Authorized = "No"
rs!showrecord = "Yes"

rs.Update

userdept = (DLookup("User_Unit", "tbl_Staff", "User_Login_ID='" & Forms("crntuserhiddendetails").Caption & "'"))

currenttime = Now()
crntusername = Forms("crntuserhiddendetails").Caption

' the below statement adds new record to the audit trail table
esqls = "insert into tbl_editrecordhistory(Txn_num, Staff_id, staff_unit, DateandTime) values('" & Txn_number & "', '" & crntusername & "', '" & userdept & "','" & currenttime & "');"
DoCmd.SetWarnings False

DoCmd.RunSQL esqls

DoCmd.SetWarnings True




'Start of code

'strBody = (DLookup("User_Email", "tbl_Staff", "User_Login_ID='" & Forms("crntuserhiddendetails").Caption & "'"))

userdept = (DLookup("User_Unit", "tbl_Staff", "User_Login_ID='" & Forms("crntuserhiddendetails").Caption & "'"))

If userdept = "Export" Then
authgroupemail = (DLookup("EmailGroup_Authorizers", "tbl_default_email_addresses", "Department_Name='" & "Export" & "'"))
Else
If userdept = "Import" Then
authgroupemail = (DLookup("EmailGroup_Authorizers", "tbl_default_email_addresses", "Department_Name='" & "Import" & "'"))
Else
If userdept = "Guarantees" Then
authgroupemail = (DLookup("EmailGroup_Authorizers", "tbl_default_email_addresses", "Department_Name='" & "Guarantees" & "'"))
Else
If userdept = "Murabaha" Then
authgroupemail = (DLookup("EmailGroup_Authorizers", "tbl_default_email_addresses", "Department_Name='" & "Murabaha" & "'"))
Else
If userdept = "Customer Service" Then
authgroupemail = (DLookup("EmailGroup_Authorizers", "tbl_default_email_addresses", "Department_Name='" & "Customer Service" & "'"))
End If
End If
End If
End If
End If


'Creates an instance of Outlook
Set objEmail = objOutlook.CreateItem(olMailItem)


'Creates string with email address
objEmail.To = authgroupemail ' "ashfaqalirizvi@gmail.com"
objEmail.Subject = "Transaction Number: " & Me.Txn_number & " Date: " & Me.Txn_Date & " Customer: " & Me.Customer_Name
objEmail.Body = "A New Transaction for subject customer has been initiated by the Processor: " & Me.Processor_Name & " and is awaiting authorization Details are...." _
& " Transaction Amount : " & Me.TXN_CCY & Me.TXN_Amount & " Value Date: " & Me.Value_Date


objEmail.Send

'DoCmd.GoToRecord , "", acNewRec



MsgBox "Email has been sent successfully to your Units Authorizer Group!! ", vbOKOnly, "Confirmation..."

DoCmd.Close
DoCmd.Close
DoCmd.OpenForm ("UserMainMenu")


If (MacroError <> 0) Then
Beep
MsgBox MacroError.Description, vbOKOnly, ""
End If




cmdaddnew_Click_Exit:
Exit Sub


cmdaddnew_Click_Err:
MsgBox Error$
Resume cmdaddnew_Click_Exit


End Sub

[Micron]

Turning off warnings often covers up a problem. Perhaps the record isn't being added as you say. On the other hand, maybe you have the table open when this runs and when you activate that window again you're not seeing the addition. You would have to refresh the view.

Better to use the Execute method of the CurrentDb object and include the parameter to provide a warning. Since you've declared and set the CurrentDb object, it would look like

db.Execute (your sql statement or variable goes here), dbFailOnError

. Otherwise, don't turn off warnings and see what happens.

Please use code tags for anything more than a few lines. Note also that long code segments tend to get broken apart by this forum, so spaces may appear where you have none, so that's another reason to use them.

Also, with respect to Dim strEmail, strBody, userdept, authgroupemail, currenttime, crntusername, expr As String
the only thing that is a string variable here is expr. All the rest are of the Variant type because you have not explicitly declared them. It's not how to make multiple declarations on one line:
Dim strEmail As String, strBody As String, userdept As String, authgroupemail As String, currenttime As String, crntusername As String, expr As String
(assumes all were supposed to be strings)
I would break the above onto at least 2 lines.
You might also want to check out how to use the Select Case block rather than so many If's.