Question Hiding a record based on a button click + reports (1 Viewer)

[A.Ali]

Dear Ladies & Gentlemen,

i have a quick question, i'm new to access and wanted to make an inventory store option anyway all is done except that option i want to add as follows.

the form have a check out Button. i want any person clicking on a record to check it out that it records the date and time of the transaction= date and time of pressing that check out button and that the record directly HIDE only from my query view BUT not to be deleted just only to be hidden.

also to create a report view/print option one with the hidden records which prompts the person for a date range i.e from 2nd of july till 7th of july ALL records that's checked in and out

and another report view/print for those still not checked out = visible records "no date range required"

attached is my DB for better understanding.

best regards,
A. Ali

 

[The_Doc_Man]

The easiest way to do the function you are describing is to add a Yes/No variable to the records for which this applies, call it "RecHidden" or something like that, and have the button click set that variable for "Yes" (TRUE) when you want to hide it.

Then in all your queries, forms, reports, etc. where you want to honor the button click, include the new field in the query or .rowsource string with a WHERE clause component such as "WHERE ( [RecHidden] = False )" - which will hide the records marked for hiding. But then the "supervisor's view" has the same query and merely includes that flag to show you that someone hid the record.

 

[A.Ali]

sorry as iam a bit new to access and VBA's , so what is the command i need to use to make the button set the field RecHidden to true and then Hide the entire record, \ i suppose after that action i would do a requery so it would hide it in the subform i have.

Regards,
Ali

 

[The_Doc_Man]

Your supposition is correct regarding the requery.

As to the code, it depends on where the click would occur. Unfortunately, at my site I cannot download databases because my security guys go bonkers if I do.

 

[A.Ali]

just a general code if it's in the middle of others like below :

Private Sub cmdAdd_Click()
'when we click on button Add there are two options
'1. for Insert
'2. for Update
If Me.lgTag.Tag & "" = "" Then
'this is for insert
'adding data to table
Dim db As Database
Dim rec As Recordset

Set db = CurrentDb
Set rec = db.OpenRecordset("Select * from StoreApp")

rec.AddNew
rec("lgTag") = Me.lgTag
rec("gstName") = Me.gstName
rec("noPcs") = Me.noPcs
rec("rmNum") = Me.rmNum
rec("stBy") = Me.cbostBy
rec("lgRack") = Me.cbolgRack
rec("chkinDate") = Date
rec("chkinTime") = Time()
rec.Update

Set rec = Nothing
Set db = Nothing

Else
'otherwise (Tag of lg Tag stores the luggage tag number to be modified)
CurrentDb.Execute "UPDATE StoreApp " & _
" SET lgTag=" & Me.lgTag & _
", gstName='" & Me.gstName & "'" & _
", stBy='" & Me.cbostBy & "'" & _
", lgRack='" & Me.cbolgRack & "'" & _
" WHERE lgTag=" & Me.lgTag.Tag

End If

'refresh data in list on form
StoreAppSub.Form.Requery

End Sub

Private Sub cmdchkOut_Click()

code goes in here


End Sub

Private Sub cmdClear_Click()
Me.lgTag = ""
Me.cbostBy = ""
Me.cbolgRack = ""
Me.gstName = ""
Me.noPcs = ""
Me.rmNum = ""



'focus on Luggage Tag txt Box
Me.lgTag.SetFocus
'set Edit button to Enabled
Me.cmdEdit.Enabled = True
'change caption of Update button back to normal of ADD
Me.cmdAdd.Caption = "Add"
'clear tag on lg Tag for reset new
Me.lgTag.Tag = ""
End Sub

Private Sub cmdClose_Click()
DoCmd.Close
End Sub

Private Sub cmdDelete_Click()
'delete record
'check existing selected record
If Not (Me.StoreAppSub.Form.Recordset.EOF And Me.StoreAppSub.Form.Recordset.BOF) Then
'confirm deletion of record
If MsgBox("Are you sure you want to delete?", vbYesNo) = vbYes Then
'delete now
CurrentDb.Execute " DELETE FROM StoreApp " & _
" WHERE lgTag=" & Me.StoreAppSub.Form.Recordset.Fields("lgTag")
'refresh date again on list "Sub Form View"
Me.StoreAppSub.Form.Requery
End If
End If

 

[A.Ali]

i tried adding it but it did not work.

any help is much appreciated.

Regards,
Ahmed Ali