[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

Hi

My form login in my Ms Access application hide immediately after login in a user because I use it to reference the user name control and store the names of the users and those approving documents in the table  called tblcontrol for purpose of a proper audit trail. Now I want to reference also all the massages so that they become personalized, I have massages like:
 MsgBox “Please check your book keeping entries are not balancing”
Now I want personalize the massage by ensuring that it pick the user name as the starting word like below:
MsgBox “Chris please check your book keeping entries are not balancing”
How can I insert this reference like below within the above message?
Forms!FormName!ControlName.Value
What I want to achieve is that all error messages must be personalised, this will help the users to think psychologically that the system knows exactly what they are doing:
I want to replace the   (MsgBox “Chris please check your keeping entries are not balancing”) with
MsgBox “Forms!FormName!ControlName.Value please check your keeping entries are not balancing”
 but does not work any suggestion here or is there other ways of achieving that?


Regards

Chris
0
I had this question after viewing Out of Memory Error when trying to compile MS Access 2013.

I have workstations running windows 10 x64 office 2016 x32. most of my workstations have 32gb of ram installed on them. i have a front end db which is at 100mb and a backed db at 600mb. i get an out of memory error periodically when trying to import forms in the the front end and then run a compiler on the db to resolve bugs and issues. i have tried doing a decompile, compact and repair, then recompile and get the out of memory error. what steps can i make to resolve this issue?
0
I have a form that has a textbox on it. The textbox has code in the OnClick event and that code is firing when I click the textbox. The weird thing is that the textbox is disabled and the code is still firing when I click the textbox. I even put code in the OnClick event that displays the enabled status of the textbox and it shows that enabled=false.

I copied this form (in the same database) and the OnClick event doesn't fire on the copy.

Any idea what my cause the OnClick event to still fire on a control that is disabled?
0
Dear All,

I am on Access 2016 and I would like to run some custom code when the user clicks on Shift and P (holds down Shift and then press P). I cannot use AutoKeys as +P does not work and Access tells me to use SendKeys, so how can I do it (SendKeys does not work in this case, as it is only sending the keystrokes, but not instructing Access how to respond to them).

Regards,
George
0
Hi Experts,

I need to compare two files containing same data with different format.

They have following columns

Patients Name
Caregiver name
Visit Date
Visit time from
Visit Time to

would need to get a list of all unmatched records.
its only considered a match if all 5 fields are the same.

Attached sample of those files, where record in both files is considered a match.

Here are the differences, one of the files has the following
1- first and last name concatenated into one field.
2- has data in parenthesis after the names which should be ignored.
3- format of time is 1459-2304
Visit-Report.xls
V_TestHHASchedule.xls
0
Need help with a difficult sql statement combined in a string where I'm using Between DateAdd with giving the correct syntax:
Here is the SQL string in code:

      Dim sDateEnding As Date
      sDateEnding = GetProperty("TimesheetWeekEnding", "")
      lType = Nz(rsType.Fields(0), 0)
      sSQL = "SELECT TimeSheetData.TimeTypeID, TimeSheetData.EmployeeID, TimeSheetData.JobTicket, TimeSheetData.AdminID, TimeSheetData.TimeTypeID, TimeSheetData.WorkDate, TimeSheetData.WorkHours, TimeSheetData.WorkDescription, TimeSheetData.Overtime, TimeSheetData.Standard, TimeSheetData.AMHours, TimeSheetData.PMHours, TimeSheetData.Posted"
      sSQL = sSQL & " FROM TimeSheetData"
      sSQL = sSQL & " WHERE TimeSheetData.EmployeeID = " & GetProperty("EmployeeID", "")
      sSQL = sSQL & " AND ([TimeSheetData].[WorkDate] Between (DateAdd(" & Chr(34) & "d" & Chr(34) & ", -7, #" & sDateEnding & "#) And #" & sDateEnding & "#))"
      'sSQL = sSQL & " TimeSheetData.TimeTypeID = " & lType
      sSQL = sSQL & " ORDER BY TimeSheetData.WorkDate"

Open in new window


The error I get is:

Error No: 3075 in GetTimeSheetData procedure;  Description:  Between operator without And in query expression 'TimeSheetData.EmployeeID = 24 AND [TimeSheetData].[WorkDate] Between (DateAdd("d", -7, #11/23/2018#) And #11/23/2018#)'

Just need the correct syntax - Not able to use the query SQL in this case cause it's using a custom parameter.
Thank you for your help in advance!
0
I have a database (see attached) that has employees and tools. Each tool can be assigned to one employee but it can also be assigned to no employee. So there's an EmployeeID field in the Tools table that either has an employee ID in it or is null.

I have a query of Employees that has a field called FullName that is defined as [LastName] & ", " & [FirstName]

Lastly, I have a query built on the Tools table and the Employees query that displays the tool data and also the FullName of the employee the tool is assigned to. The query is set to show all records from the Tools table and only those records from the Employees table where the values are equal.

My issue is that if the tool isn't assigned to any employee, the FullName in the Tools query shows up as just a comma. I would have expected it to be completely blank since there's no record in qryEmployees that associates with this.

If I change the join in the query to only show only show records in both tables where the joined fields are equal, it leaves the records with no employee ID out of the query (as expected).

You can see this behaviour by opening qryTools in the attached database.

I don't know why it's showing the calculated FullName field for records that don't exist in the Employees query.

Any suggestions about why this is happening and how I can stop it?

Thanks in advance.tools.accdb
0
How can I prevent users from opening an accde more than once on a Windows PC?  Some users open it on each monitor and this is causing db corruption and other strangeness.

Thank in advance!
0
Hi

Our government customers are very angry with Microsoft, but I think this is nothing new as we know such problems with Windows 10.
I have to analyze this problem and advise our customers.

According to the Dutch organization that did the research for the Dutch government, we have the following infos:

-  Microsoft systematically and extensively collects data about the individual use of Word, Excel, PowerPoint and Outlook. Secretly, without informing the people. According to the Dutch organization, it is hard to find what Microsfoft collects as the data is encrypted.
-  Microsoft sends telemetry data to its own servers in the United States.

This is what I found that we can do for our customer:
- Disable Manage the privacy of data monitored by telemetry in Office
- Ban the use of Connected Services
- Not to use SharePoint Oneline
- Block OneDrive
- Lock web version of Office 365

My questions:
1) Did you deal with this case? If yes, what will Microsoft collect?
2) Is it enough to Manage the privacy of data monitored by telemetry in Office ?
3) Do I still have to go through the Report from rijksoverheid.nl  ?

Thanks
0
On a report I have a textbox and a label to the left of the textbox.   If no data exists in the textbox I want the whole line to shrink vertically to '0" height.  I've tried this but it doesn't work:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    If IsNull(Me.txtAddlProofNote) Then
        Me.Label20.Caption = ""
        Const TWIPSTOINCHES = 1440
        Me.txtAddlProofNote.Height = TWIPSTOINCHES * 0
        Me.Label20.Height = TWIPSTOINCHES * 0
    End If
    
End Sub

Open in new window

0
We use a back end / front end Access 2016 database.  The frontend contains the forms, whereas the backend stores all of the data.  This has been working well for the past 8 months.  Two weeks ago, we upgraded from a Windows 8 file server to a Windows 2016 domain environment.  All 10 workstations were added to the domain, along with 15 new user accounts.  All of the files were successfully migrated to the new server using robocopy.  We also implemented a GPO to enable Folder redirection for the users' Desktop, Document, Favorites, Photos, Music and Videos.  We also have a GPO to map network drives.

Since the move, users are receiving the following error about once an hour when using the FrontEnd, "Your network access was interrupted".  We applied the following changes to try and rectify the issue, but nothing appears to help:
  • Turned off NIC Teaming on the server
  • Copied the database to the local C Drive on each workstation (instead of the user's desktop, which resides on the server)
  • Updated the Frontend so that it uses \\UNC instead of the Mapped drive

None of the networking hardware has changed.  All workstations are connected via RJ45 to a Netgear JGS524NA switch, and a Fortigate 60E router.  We have had no other network related complaints other than a few random instances where the office chat software could not connect to the office shat server (external server).  There have been no reports about access the share …
0
How do you write a simple VBA coding for check boxes to filter data in a listbox.

On Form A I have several categories to check Category A, B, C that is base on yes/no. I want to make sure when I check Category A, just A shows on Form B. If left on unchecked then all categories show.
0
I need to use a datasheet subform's .filter property to generate filtered reports and other database actions.  However when I clear the filter using the attached code two things happen:
1) The actual subform is displayed with the filter cleared.
2) The subform's .filter property string still has the old value.

The same scenario occurs if I use the Ribbon icon to Toggle the filter and still the .filter string is not cleared.

 Snapshot showing filter is not cleared
I can clear the field in the Immediate Window just fine, just not with code.

Manipulating the datasource with programatically generated SQL is impractical.

Any ideas?
0
I'm getting a Record too large error when I try to append one of my tables to a new table. I'm using MS Access 2K.  I suspect one of my users entered too much data into one of my fields.
I (1) need help finding the record causing the error... and (2) suggestions how to prevent it from happening again..

I know about the "2000" character limitation of Access records, but I can't find the offending record... it seems like someone must have written code???

I don't really understand Unicode Compression . it seems like it can affect this problem?  Can I mitigate the problem by changing a Unicode setting???  I don't need support for any language other than English.

I know I can delete unused fields, change to memo type, and normalize, but I right now I just need help finding the offending record.
Thanks in advance.
0
I am using the controls property of a report similar to :

  Set rpt = Application.Reports(MyReportName)
  For Each ctrl In rpt.Controls
      ' do something
  Next

However, i would like the rpt.controls to be sorted by ctrl.name prior to the For Next loop - is there a simple way to achieve that or do I have to load it into an array first?
Thank you for any help!
Regards Michael
0
This isn't for work, just something I'm playing with in Access 2013

I am developing a routine that needs to read thru all files in a directory.

This is what the routine needs to do:

Delete any files in the directory with the string "_2", _3", "_4", "_5" or "_6" embedded in the file name as these are duplicates.

Rename each file in the directory by manipulating the name.

The contents of the directory are a list of songs that I've downloaded.  The files are named [Artist] - [Song Name].mp3

I want to rename them to [Song Name] - [Artist}.mp3

Here's some examples of the actual file names.  They all fit this pattern.

The title of the song and the contributing artist are apparently in the file information (the "Title' and "Contributing Artist" columns/metadata?) but I don't know how to access this information for each file in the table.

Directory Format


Any help would be appreciated.
1
Access 2003
I set Breakpoints, but when I run the Subroutine, the program does stop at the Breakpoints
Very simple question:  How does one enable breakpoints
0
Hi

In Access 2010 Professional a user using my Access solution gets the following error while trying to open a report with a query behind it.
Someone else in his office also running Access 2010 does not get any error and I don't get any error.

The expression in one of the queries that I believe is causing this is as follows

InvNum: Nz(DLookUp("[Invoice No]","Invoices","[ID] = " & Nz([Invoice ID],0)),"0")

Open in new window


Can I change my expression to something else that doesn't use Nz?

1
0
Hi,
I would like help to amend a formula to speed up calculations with an INDIRECT function.
Please refer to attached spreadsheet.
Many Thanks
Ian
Indirect-Function.xlsx
0
in excel how to represent below date and time stamp as it is like in database cell

2018-11-13 06:13:15.891


when i copy past it shows as 13 or something

how to import sql server results value including heading to excel without loosing date formatting etc
any tips or tutorial videoes around this?

Please advise
0
I have a client who has an application which involves a combination of VBScript and Access to download data from an FTP site and then process that data into multiple Access databases. I'm not happy with the way this was implemented but I'm stuck with it.

I received an email this afternoon which indicates that about 15-20 of the files that should have been downloaded and processed on Monday did not make it into the Access databases.

Indications are that something occurred on there servers which affected their authentication service.  Any chance that this would have affected ADO communications between the VBScript and the Access BE databases?  I'm not a huge fan of VBScript or ADO and don't use either often enough to know what will or will not affect them.

Dale
0
Hi, I'm trying to get an update query to work!  I have 2 tables with the destination table that has a date field (datetime)   (on an MSSQL server) and  the sourse is an Excel file I linked to in Access.

In my Query:

Field: Start_date
Table:  Monthly_rep
Update To: [Excel_rep].[start_date2]

the update To:  I tried to put in CAST , CONVERT  etc keep getting error!  

CAST([Excel_rep].[start_date2] as DATE)
CAST([Excel_rep].[start_date2] as DATETIME)
Cdate([Excel_rep].[start_date2])

ETC.

Thanks

Pete
0
Access vba

Using ian INSERT statement in vba.

What I need: sometimes the  " InProgress_or_Closed_at" value does not get inserted?
i'm i using the  "Now()"  correctly in the sql statement ?

Or is there a better way to write it to make sure a time stamp is posted correctly ?


ttt = Forms!dataentry.txtReOpened
                    strsql_sql = "INSERT INTO [dbo_t_nsc_trackcode_trans_time_entry](NSC_ID_Ref, [opened_at], InProgress_or_Closed_at)" & vbCrLf
                    strsql_sql = strsql_sql & "VALUES (" & Me.NSC_Id & ",'" & ttt & "' , now());"
                     CurrentDb.Execute strsql_sql, dbSeeChanges

Open in new window



Thanks
fordraiders
0
Hello,

My company's MS Access VBA software uses Azure Blob Storage and allows users to upload files from their computer to our blob storage. I have a user that requested for the file dialog box filter to default to what they last selected. Currently the filter always defaults to Image files and usually this user uploads PDFs. Is there anyway to get the filter to default to the last filter the user selected? Please see attached file to view the current code for the file dialog.

Thanks in advance!
File_Dialog_Code.PNG
0
I have two queries that return:

Gains Averaged
Losses Averaged  
And I want the final result to look like this:

Final Result
 

How can this be done?
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.