[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Microsoft Access

220K

Solutions

51K

Contributors

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Share tech news, updates, or what's on your mind.

Sign up to Post

I have designed a custom form in Access 2016 with text boxes for search criteria.
I need vb code to query the access database and populate a list box on the form with field data that matches all data in text boxes.
0
IT Pros Agree: AI and Machine Learning Key
LVL 1
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

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
0
Hi Experts,

In Access 2010 I want to get a list / log file of all occurrences of a field or table within a Project.

The output needs to identify the Source Object (e.g. Form / Report / Table / Module) where the field / table exists.

Is there a way or a utility that I can buy / download that will do this for a reasonable cost?

Thanks,
Bob C.
0
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
0
Is it possible for Access to open a file straight from Onedrive, like Word can? I have attached an example screenshot of the way Im trying to open it.
When I try to do that, it thinks about it for a few seconds, then comes up with a message that says "The file does not exist, or you do not have read access to the file".

example.png
I have tried it on two computers and both did the same thing. If you do the same process to open a document in Word, it works just fine.
It will open if I browse to a copy of the file saved locally.

Im thinking maybe Access just doesn't support open files straight from Onedrive? I was hoping to avoid having a copy saved locally because the Onedrive account that is linked with Office is different to the Onedrive account linked to the computer and you cant sync two personal Onedrive accounts at the same time.
0
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
0
I have a split form where I would like the user to click on a button and a new frame opens where he can choose an existing client. This new client id is then returned to the calling frame. So far so good.

After this frame closes and a new Client ID has been received I need to insert a new record into the existing datasheet using the supplied client id. Unfortunately, I do not know how to kick off this processing once the CLIENT frame has been closed and the new client id has been stored in a variable on the frame? Any help which event I could/should use? Sorry if it sounds a bit confusing..

Thank you so much for your help! Regards Michael
0
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
0
Hi

Using IDBE Ribbon creator. I have a working copy and want to add another button, what is the best way to do this without disrupting the OnActionButton(Control). every time I try the button gets added but  the previous info in  the OnActionbutton in the module basRibbonCallbacks  gets wiped.


Any help appreciated


chestera
0
How to find missing number in a sequence by applying query in Ms Access. Any help will be highly appreciable.
0
The Five Tenets of the Most Secure Backup
LVL 1
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

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 …
0
Hi Experts.

In Access 2010 when I delete a record in a form and then browse to the deleted record it displays #deleted.

Is there a method to stop this from displaying?

I believe that it results from Access doing a logical rather than a physical deletion until something happens like the form is closed (which would also close the table).

If this is the case is there a way to 'force' the closing?

Thanks,
Bob C.
0
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?
0
MS Access crashes when attempting to attach SQL Server tables using ODBC
0
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
0
I have a continuous form on an Access form. One of the controls on it is a DateTo textbox and another one is an Edit button. The button is hidden by default.
How do I set it visible if the DateTo is greater than today's date or empty?

I tried to loop through the recordset, but it doesn't work. To see which records are affected, I have changed the background colour of one of the controls.

Annotation.png
I used the following code to loop through the form:

Sub ShowHideButton()

With Me.Recordset
  .MoveFirst
  Do While Not .EOF
    If kem_DateTo.Value >= Date Or kem_DateTo = " " Then
      Me.cmdEdit.visible = True
      Me.kem_DateTo.BackColor = vbRed
    End If
    .MoveNext
  Loop
 
End With

End Sub

Open in new window


Is it possible to archive this with VBA or would I have to use another technique?

Thanks
Massimo
0
access 2016 show same fields but different rows/data on same page report
0
I have a table in Access with 3 columns: first is transactionID, the second is textline, 3rd is msg.  check example below pls            
            
transactionID      textline      msg
564231      3      hello
564231      4      hi
564231      5      hello
564231      9      hi
564202      1      hello
564202      2      hi
564202      8      hello
564202      22      hi
513830      7      hello
513830      10      hi
513830      13      hello
            
I would like the result to be as follows            
Return the transactionID, textline, msg for  those with consecutive textlines per transactionID            
            
transactionID      textline      msg
564231      3      hello
564231      4      hi
564231      5      hello
564202      1      hello
564202      2      hi
0
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
0
Starting with Angular 5
LVL 12
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

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
0
Hi, I am not quite sure how to come about the query for the intended results, am not sure if that is possible, hopefully Gurus here can help out.
Both Table share the same structures, LinkedTbl and LocalTbl

LinkedTbl                                                             LocalTbl
StampedId           Client                               StampedID           Client
      31                     Client1                                       31                     Client1
      32                     Client2                                       32                     Clinet2
      33                     Client3                                                                Client3
      34                     Client3                                                                Client3
      35                     Client3                                                                Client3

I tried to create a query to update the blank StampedId field from LinkedTbl to LocalTbl so that I able to get the following end results, but it was updated with the last number (35) from LinkedTbl to the null StampID field in LocalTbl, perhaps due to duplicate Clients in Client Column (must allowed duplicate Client entry in column), there are no other field in both Tbls allowed for index.

Supposed results after update query:

LinkedTbl                                                             LocalTbl
StampedId           Client                                  
0
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
0
Hi There,
I like to find how many weeks exist in each month for a year. I have query for each month. I have query in access (summing the qty each month for each Item) for each month January through December from a table that has Qty for each item for  each day.

The query has the year, month, Total Quantity. For example the Jan query would have the following fields
Yr        Mth   Qty    Item
 2018    1      10000   ABC

I would have to find the weekly quantity for that I would have to divide this qty by the number of weeks each month has in a year and it differs each year.
so I like to see the query result with another field MaxWk ( that will tell the total week in that month for the year)
I tried the datediff funtion doesn't work .I tried MaxWk=  DateDiff("ww",1/1/[yr],1/31/[yr],2), just returns 0  
Any idea how I can do this?
Thanks
0
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.
0
In MS Access, I have a webcontrol on a form.  I want to be able to force magnification for the URL to 125 percent.  I have found several articles on Google about this, but nothing seems to work for me.  Here is what I have now - the last line represents the best suggestion I was able to find on the intrawebs:

DoCmd.OpenForm "WebBrowser", acNormal
DoCmd.Maximize
       
Forms!WebBrowser.RecordSource = "Wordpress"
    Forms!WebBrowser.Filter = "[Select]=-1"
    Forms!WebBrowser.FilterOn = True

'''''  the URL comes from tbWordpress from either be in Site, NewPost or NewPage in the Wordpress table
    Forms!WebBrowser!WebBrowser0.ControlSource = "NewPost"
   
   
    Forms!WebBrowser!WebBrowser0.ExecWB OLECMDID_OPTICAL_ZOOM, ExecOpt.OLECMDEXECOPT_DONTPROMPTUSER, 150, DBNull.Value
0

Microsoft Access

220K

Solutions

51K

Contributors

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.