Hi

Kindly help me with the code below I want a situation where if a user select a wrong range then the code should just exit instead of bringing the debug or end message :

Private Sub txtSalaryEndDate_LostFocus()
Dim Cancel As Integer
Dim Lvalue As Integer
Lvalue = DateDiff("d", Me.txtSalaryStartDate, Me.txtSalaryEnddate)
If Lvalue >= 32 Then
Beep
MsgBox "Please select the correct date range example one month only", vbOKOnly, "Wrong Selection"
Cancel = True
Me.txtSalaryEnddate.SetFocus
Me.CboFirstName.visible = False
Me.CmdPayslips.visible = False
ElseIf Lvalue < 0 Then
Beep
MsgBox "Please select the correct date range example one month only", vbOKOnly, "Wrong Selection"
Cancel = True
Me.txtSalaryEnddate.SetFocus
Me.CboFirstName.visible = False
Me.CmdPayslips.visible = False
End If
End Sub

What I'm looking for is how to incorporate the error handling within the above code so that debug message does not pop up , I do not like this pop up because it allows the users to start tempering with the code.

Regards

Chris

I have the following code block that takes the input from a form (frmSearchMediaTitles) to conduct a search based on the information gathered.  There are two values passed from the form the search type (vMediaTitleSearchType) and the search criteria (vSearchTerm)

I need to trap for the following errors.  If either or both vMediaTitleSearchType and vSearchTerm are null then I want a message box to pop and explain the error and give the user the option to exit or go back to the form and correct the error.

I will also need to trap for an error if nothing is found that matches the search criteria.  I believe this will involve setting up a DAO recordset.  The code below only chows one CASE statement, when completed there will be 6 case statements that will need this type of error handling.




Private Sub btnSearch_Click()

    Dim vMediaTitleSearchType As String
    Dim vSearchTerm As String
    Dim vSQL As String
 
    vMediaTitleSearchType = Me.MediaTitleSearchType

          Select Case Me.MediaTitleSearchType
           
                Case "Title"

                        vSearchTerm = Me.SearchTerm
                        vSQL = "Title LIKE '*" & vSearchTerm & "*'"                    
                        DoCmd.Close         'Close form frmSearchMediaTitles
                        DoCmd.OpenForm "frmTitles", , , vSQL
 
        End Select

I have a table tblTitles.  I have created a form for searching different information in this table.  The two values on the search form are MediaTitleSearchType which the user will select the field to search on and SearchTerm which will contain the value to be searched.  On the search form are two buttons  "Search" and "Cancel".  In the click event procedure for btnSearch I will set up a Case statement to run the proper code as selected in MediaTitleSearchType.  Clicking the "Search" button will run the code and open the form frmTitles showing only the records that fit the search.

My problem seems to be in the line   vSQL = "Title " & " Contains " & " 'vSearchTerm'" where vSearchTerm is not showing the value of the variable but just the string vSearchTerm.

How do I correct this?

Private Sub btnSearch_Click()

    Dim vMediaTitleSearchType As String
    Dim vSearchTerm As String
    Dim vSQL As String
   
    vMediaTitleSearchType = Me.MediaTitleSearchType

          Select Case Me.MediaTitleSearchType        
                Case "Title"
                        vSearchTerm = Me.SearchTerm
                        vSQL = "Title " & " Contains " & " 'vSearchTerm'"
                                                       
                        DoCmd.OpenForm frmTitles, , , vSQL
                                     
        End Select

End Sub

Hi everyone

I have been asked to create a database which will be used by several departments.
My question is about queries in general.

As someone else might make changes in the future, I want to keep it as maintainable as possible.
Should I create a query object for every (SELECT) query I do (some forms might have several)?
Is there something like a golden rule / best practise or does it not matter?
Or maybe I should ask the question another way: What would you like to see when you take over someone else's Access project?

Thanks

Massimo

How to find missing number in a sequence by applying query in Ms Access. Any help will be highly appreciable.

Hello Experts:

I need some help with some data management (using Excel and Access).

Background on Excel File (incl. two tabs "Raw Data" and "Summary"):
- The "Raw Data" tab contains four columns [Incident Date], [Incident Time], [Age], [Gender]
- Also, for each of these four columns, I added an adjacent "Delta" column using an IF statement to compare two adjacent cells (per row).
- For example, for the [Date] colummn, in cell B2, I compare cell A2 with A3.  If date = 07/01 = 07/02, I output either "Same" or "Different".  In this case, cell B2 returns "Different".
- Alternatively, in cell B3, I compare cell A3 with A4.  If date = 07/02 = 07/02, I output either "Same" or "Different".  In this case, cell B2 returns "Same".
- The remaining formulae (in columns D, F, and H) follow the same principle as applied in column B.
- Now column I... it's using a nested IF formula.   I want to determine where columns A, C, E, G are all the "Same" (or "Different").
- In this case, out of 323 rows, 90 rows are exactly the "Same" and 233 rows equal "Different" (see tab "Summary").

Background on Access file:
- I imported the Excel data from "Raw Data" (without the "Delta" columns).   Table "00_tblRawData_323_Records" includes 323 records.
- I created a Select/Make Table query ("00_qry_233_Records") which uses the "Group" feature in the query.  Upon executing the MakeTable query, it now creates table "01_tblRawData_233_Records".  
- The records in …

I have a very long horizontal form.  When I click a command button on the form I want the focus of the form to jump down to a designated spot on the form.  Can this be done?

I am getting old but somehow the following code does not yield the desired result:

xName = "Steven Murray Thomas"
MsgBox InStr(1,xName," ")

gives zero and not the expected 7. Any help why?
Thank you so much Michael

access 2016 show same fields but different rows/data on same page report

I have a current Access DB saved an *.accdb.  I am trying to convert this DB to the *.mdb format.  However the current Access DB has embedded macros within it.  How can I disable these macros so that I can convert this Access DB into the older MDB format??

Sincerely,

Patrick Q

PATRICK QUINN  
SENIOR DATABASE DEVELOPER
NA ALIʻI, LLC
An 8(a)ccessible Native Hawaiian Owned Company

Hi
When I use following formula to convert text date to date format, I get #Error wherever text date is blank. I need null instead of #Error

Base Date: CDate([QryRprtDtl].[BsDate])

ie.

text date     Base Date
09/10/18         09/10/18
                         #Error

Please have a look.

Thank you
A

Hi Experts,

I have a continuous form in my application containing a few text boxes that are set for conditional formatting.

While the application was in MDB format (A2003) it worked fine.

However since converted to ACCDB (A2016) the form is loading/formatting records extremely slow.

Every time user filters or sorts it keeps repainting the entire screen several time, moving thru all records up and down...

What is the solution?

Attached screenshot of what the form looks like.
Untitled.png

I have a continuous form that has a field that is acting strange.  There is one field in the form that only displays values when the scroll bar is clicked.  When it is not clicked the values disappear.

What would be causing this?  I have no formatting going on anywhere.