Stuck when press enter (Instant search form) (1 Viewer)

A

Alhakeem1977

Registered User.
Local time
Today, 16:43
Joined
Jun 24, 2017
Messages
308
Hi All,
I have got a Microsoft Access Datasheet instant search form.

When the user type in a text box called [txtSearch] (on Change event ) the form will filter the records instantly.

The problem is that when the user press Enter (as a normal practice) the form is getting stuck.

Any suggestions would be highly appreciated.

Thanks in advance!

Sent from my HUAWEI NXT-L29 using Tapatalk
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 06:43
Joined
Oct 29, 2018
Messages
21,449
Hi. We might have to see the code you're using for the search to get an idea why it's getting stuck.
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:43
Joined
May 7, 2009
Messages
19,231
only suggesting, disable the return Key from the search textbox, using the textbox KeyDown event:
Code: 
Private Sub TextSearch_KeyDown(KeyCode As Integer, Shift As Integer)
      If KeyCode = vbKeyReturn Then
            KeyCode = 0
      End If
End Sub
 
A

Alhakeem1977

Registered User.
Local time
Today, 16:43
Joined
Jun 24, 2017
Messages
308
theDBguy said:
Hi. We might have to see the code you're using for the search to get an idea why it's getting stuck.
Click to expand...
Private Sub txtSearch_Change()

DoCmd.Requery "qrySub subdormDC"

End Sub

Sent from my HUAWEI NXT-L29 using Tapatalk
 
A

Alhakeem1977

Registered User.
Local time
Today, 16:43
Joined
Jun 24, 2017
Messages
308
arnelgp said:
only suggesting, disable the return Key from the search textbox, using the textbox KeyDown event:
Code: 
Private Sub TextSearch_KeyDown(KeyCode As Integer, Shift As Integer)
      If KeyCode = vbKeyReturn Then
            KeyCode = 0
      End If
End Sub
Click to expand...
Thank you so much [emoji817]
It's working [emoji122]


Sent from my HUAWEI NXT-L29 using Tapatalk
 
A

Alhakeem1977

Registered User.
Local time
Today, 16:43
Joined
Jun 24, 2017
Messages
308
It's solved.

But I do not know how to mark it as Solved[emoji51]

Thanks Arnelgp for your help.

Sent from my HUAWEI NXT-L29 using Tapatalk
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 06:43
Joined
Oct 29, 2018
Messages
21,449
Alhakeem1977 said:
It's solved.

But I do not know how to mark it as Solved[emoji51]

Thanks Arnelgp for your help.

Sent from my HUAWEI NXT-L29 using Tapatalk
Click to expand...
Hi. Glad to hear you got it sorted out. Good luck with your project.
 
A

Alhakeem1977

Registered User.
Local time
Today, 16:43
Joined
Jun 24, 2017
Messages
308
Thank you so much theDBGuy

I have an issue wirh other database for multi users SubForm data entry form that the users still getting a duplicate [DocID] the sequential document ID please find below my code.

The Error 3022 should work but it didn't

Code: 
'------------------------------------------------------------
' Save_Click
'
'------------------------------------------------------------
Private Sub Save_Click()
On Error GoTo ErrorHandler

Dim strLinkCriteria As String
Dim DocID As Integer
Dim Cancel As Variant

If DontPromptUser = True Then Exit Sub

 If IsNull(Me!AccountNo) Then
MsgBox "You must provide an Account Number!", vbCritical
Me!AccountNo.SetFocus
' 1 '''''''''
ElseIf IsNull(Me!DocumentDate) Then
MsgBox "You must provide a Document Date!", vbCritical
Me!DocumentDate.SetFocus
'' 2 '''''''''
ElseIf IsNull(Me!DocumentName) Then
MsgBox "You must provide a DocumentName!", vbCritical
Me!DocumentName.SetFocus
' 3 '''''''''

Else
strLinkCriteria = "[AccountNo] = " & Me!AccountNo & " AND " _
& "[ActualAC] = '" & Me!ActualAC & "' AND " _
& "[DocumentDate] = " & Format$(Me!DocumentDate, "\#mm\/dd\/yyyy\#") & " AND " _
& "[DocumentName] = " & Me!DocumentName & " and " & "[Notes] = '" & Me!txtNotes & "'"

Dim varID
varID = DLookup("DocID", "tblFacilityRegister", strLinkCriteria)

If DCount("*", "tblFacilityRegister", strLinkCriteria) > 0 Then
Me.txtSeqNo.Value = ""
Me.DocID.Value = ""
Me.Save.Enabled = True
Me.img1.Visible = True
MsgBox "Duplicate document !" & vbCrLf & _
"Please write the Doc ID *( " & varID & " )* at the top and send it to DCD. ", vbCritical, "Duplicate Entry"

Cancel = True
Me.Undo
Me.txtCustomer = ""
img1.Visible = False
DoCmd.GoToRecord , "", acNewRec
Me.txtDocsToBeSent.Requery

Else
Me.SeqNo = Nz(DMax("[SeqNo]", "tblFacilityRegister", "Year([SentDate]) = " & Year(Me.[SentDate])), 0) + 1
Me.DocID = Format([SeqNo], "0000") & "-" & Format([SentDate], "yy")
DoCmd.RunCommand acCmdSaveRecord
Me.Save.Enabled = False
Me.New_Record.SetFocus
Me.txtDocsToBeSent.Requery

If Err = 3022 Then
Forms!frmFacilityDocsRegister.Refresh
Me.DocID = ""
Forms!frmFacilityDocsRegister.Refresh

 Me.SeqNo = Nz(DMax("[SeqNo]", "tblFacilityRegister", "Year([SentDate]) = " & Year(Me.[SentDate])), 0) + 1
 Me.DocID = Format([SeqNo], "0000") & "-" & Format([SentDate], "yy")
DoCmd.RunCommand acCmdSaveRecord
 Me.Save.Enabled = False
 Me.New_Record.SetFocus
Me.txtDocsToBeSent.Requery

End If
 End If
End If
Cleanup:
Exit Sub
ErrorHandler:
MsgBox Err.Number & ": " & Err.Description
Beep
 MsgBox "You must enter a value in one of the following:" & vbCrLf & _
vbCr & "Account Number, DTD or Document Name.", vbOKOnly, "Empty Fields"

Resume Cleanup
End Sub

Sent from my HUAWEI NXT-L29 using Tapatalk
 
Last edited:
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 06:43
Joined
Oct 29, 2018
Messages
21,449
Alhakeem1977 said:
Thank you so much theDBGuy

I have an issue wirh other database for multi users SubForm data entry form that the users still getting a duplicate [DocID] the sequential document ID please find below my code.

The Error 3022 should work but it didn't
Click to expand...
Hi. What error are you trying to catch (What is error 3022)?
 
A

Alhakeem1977

Registered User.
Local time
Today, 16:43
Joined
Jun 24, 2017
Messages
308
theDBguy said:
Hi. What error are you trying to catch (What is error 3022)?
Click to expand...
It's to avoid duplication in the concatenated sequential [DocID] field(I set it from the table) it's a hidden field but when the record is comitted it will be visible to the users that will be used as document reference for dispatch purpose between the departments within the organization.

Sent from my HUAWEI NXT-L29 using Tapatalk
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 06:43
Joined
Oct 29, 2018
Messages
21,449
Alhakeem1977 said:
It's to avoid duplication in the concatenated sequential [DocID] field(I set it from the table) it's a hidden field but when the record is comitted it will be visible to the users that will be used as document reference for dispatch purpose between the departments within the organization.

Sent from my HUAWEI NXT-L29 using Tapatalk
Click to expand...
Thanks. So, if you intentionally try to create a duplicate, are you saying it is not catching error 3022 and skipping all the way down to the error handler?
 
A

Alhakeem1977

Registered User.
Local time
Today, 16:43
Joined
Jun 24, 2017
Messages
308
theDBguy said:
Thanks. So, if you intentionally try to create a duplicate, are you saying it is not catching error 3022 and skipping all the way down to the error handler?
Click to expand...
Yes, it is.
When two users press the Save button at the same time it should catching the error 3022 and provide the next sequential DocID to the other user.

Sent from my HUAWEI NXT-L29 using Tapatalk
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 06:43
Joined
Oct 29, 2018
Messages
21,449
Alhakeem1977 said:
Yes, it is.
When two users press the Save button at the same time it should catching the error 3022 and provide the next sequential DocID to the other user.

Sent from my HUAWEI NXT-L29 using Tapatalk
Click to expand...
Hi. Not sure I understood that. However, when two users are trying to get the next ID value at the same time, you'll have to realize the DLookup() and DMax() functions can only retrieve information already stored in the table. If you want to know if an ID value is about to be assigned to another user, you'll need to use a different method then DMax() and DLookup().
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:43
Joined
May 7, 2009
Messages
19,231
it is always best to have a Separate table for the generated seqno, and a separate function to generate and save it.

suggested structure of seqno table:
Code: 
tablename: tblSeqNo
fieldname       fieldtype
==================
Year               long
SeqNo            long
for the sequence generator:
Code: 
Public Function fnGENseqNO(lngYear As Long) As Long
On Error GoTo err_handler
      Dim RS As DAO.Recordset
      Dim lngSeqno As Long
      Dim iCounter As Integer
      
      SysCmd acSysCmdSetStatus, "generating sequence no"
      DoEvents
      Set RS = CurrentDb.OpenRecordset("select [seqno] from tblSeqno " & _
                                          "where [year]=" & lngYear & " order by [seqno] desc;", dbOpenDynaset, dbDenyWrite + dbDenyRead)
      With RS
            If Not (.BOF And .EOF) Then
                  .MoveFirst
                  lngSeqno = !seqno + 1
            Else
                  lngSeqno = 1
            End If
            .AddNew
            ![seqno] = lngSeqno
            ![Year] = lngYear
            .Update
            .Close
      End With
exit_handler:
      SysCmd acSysCmdClearStatus
      Set RS = Nothing
      fnGENseqNO = lngSeqno
      Exit Function
err_handler:
      iCounter = iCounter + 1
      If iCounter < 20 Then '20 retries before failing
            SysCmd acSysCmdSetStatus, "error getting seqno, retrying (" & iCounter & ")"
            DoEvents
            Resume
      Else
            Resume exit_handler
      End If
End Function
as you will noticed we open the table as DenyRead and DenyWrite.
meaning no other person can read or write to this table as long as you have it opened.
in case you have error (cannot open or write when others have already opened), it will retry 20 times before exiting with seqno 0.
you call this function, test if the return value is 0. if it does its your choice to call it again or call it off.
the function is very quick since the recordset is ordered in descending order.
get the seqno, add one, save the new value, exit.
 
A

Alhakeem1977

Registered User.
Local time
Today, 16:43
Joined
Jun 24, 2017
Messages
308
arnelgp said:
it is always best to have a Separate table for the generated seqno, and a separate function to generate and save it.

suggested structure of seqno table:
Code: 
tablename: tblSeqNo
fieldname       fieldtype
==================
Year               long
SeqNo            long
for the sequence generator:
Code: 
Public Function fnGENseqNO(lngYear As Long) As Long
On Error GoTo err_handler
      Dim RS As DAO.Recordset
      Dim lngSeqno As Long
      Dim iCounter As Integer
      
      SysCmd acSysCmdSetStatus, "generating sequence no"
      DoEvents
      Set RS = CurrentDb.OpenRecordset("select [seqno] from tblSeqno " & _
                                          "where [year]=" & lngYear & " order by [seqno] desc;", dbOpenDynaset, dbDenyWrite + dbDenyRead)
      With RS
            If Not (.BOF And .EOF) Then
                  .MoveFirst
                  lngSeqno = !seqno + 1
            Else
                  lngSeqno = 1
            End If
            .AddNew
            ![seqno] = lngSeqno
            ![Year] = lngYear
            .Update
            .Close
      End With
exit_handler:
      SysCmd acSysCmdClearStatus
      Set RS = Nothing
      fnGENseqNO = lngSeqno
      Exit Function
err_handler:
      iCounter = iCounter + 1
      If iCounter < 20 Then '20 retries before failing
            SysCmd acSysCmdSetStatus, "error getting seqno, retrying (" & iCounter & ")"
            DoEvents
            Resume
      Else
            Resume exit_handler
      End If
End Function
as you will noticed we open the table as DenyRead and DenyWrite.
meaning no other person can read or write to this table as long as you have it opened.
in case you have error (cannot open or write when others have already opened), it will retry 20 times before exiting with seqno 0.
you call this function, test if the return value is 0. if it does its your choice to call it again or call it off.
the function is very quick since the recordset is ordered in descending order.
get the seqno, add one, save the new value, exit.
Click to expand...

Dear arnelgp,

Please find attached my db if you could get the opportunity to adapt your recommendation to have the temp table and function to get the seq DocID.

I know it's too hard for me to implement your suggestion.

I am sorry to request this from you, but you get time please do it for me.

For your kind info my transaction table called: [tblFacilityRegister] and the data entry form called: frmIndvDocsRegister. you will find the code in the Save button.

Thanks in advance.
 

Attachments

  • Sample Dispatch System.accdb
    1.7 MB · Views: 299
Last edited:
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:43
Joined
May 7, 2009
Messages
19,231
copy tblSEQNO to the backend and create a link to the FE
 

Attachments

  • Sample Dispatch System.zip
    572.2 KB · Views: 295
isladogs

isladogs

MVP / VIP
Local time
Today, 14:43
Joined
Jan 14, 2017
Messages
18,209
Alhakeem1977 said:
It's solved.

But I do not know how to mark it as Solved
Click to expand...

When you are ready to mark it solved, click the Thread Tools dropdown and select the menu item.
Not sure it really is solved yet so haven't done this for you.
 
You must log in or register to reply here.

Similar threads

A
On Enter Key Press-Login
Replies
3
Views
201
arnelgp
arnelgp
K
Form blank when no records found
Replies
9
Views
463
kevinh2320
K
W
Solved MS Access Criteria code for searching a number value in a FORM
Replies
2
Views
307
pbaldy
pbaldy
M
RTF textbox - report gets stuck on "formatting page"
Replies
10
Views
728
MvJ
M
A
Trouble Exporting Sorted Data from Access Form with Subforms to Excel
Replies
2
Views
85
ebs17
E

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom