Microsoft Access Help

I have a small "network" consisting of three computers and a "File Server" (peer to peer). This supports an Access 216 database. The server has a file that contains the "Back End" Tables, and each PC (Call them PCA, PCB, PCC) has an identical copy of the User Interface. This has worked well for years.

PROBLEM: All of a sudden some of the data displayed on each PC is different from data displayed on the others. Example: Updating a record on one PC is NOT reflected on the other two [b/]. The same data table can have DIFFERENT NUMBER OF RECORDS on each PC[b/]. Worse: If I open the Back End file on the "server" I find that in many cases the data there is also different from that on some or all the PC's

I HAVE MADE VERY SURE THAT THERE IS ONLY ONE COPY OF THE DATA ON THE SERVER that is accessed by all three PC's

Example The User Interface on PCA can show 100 records for the widgets table and the User Interface on PCB may show 110 records while the User Interface on PCC could have either the same number (100 or 110) or it could have 95 records.

Is it possible that the file server has created separate copies of the same table. I searched using a File Search program but could not fine any copies of the table(s).

Has anyone experienced something similar? This is only happening since I installed Access 2016. It never happened with Access 2003, 2007, 2010, 2013 etc.....

I look forward to hearing your comments.

Thanks,

Biggles1

6 Comments

Is there a way to add a button here to open a specific form?

Microsoft Access

2 Comments

I would like to reference the Primary key on the continuous long summary report on my VBA code see below so that I get the correct results per total. The report was combined using domain functions, there is no ISSUE here in terms of performance all is perfect even if there are huge queries to do the actual calculations

The report has grouped sales data for each salesman coming from un related tables & queries, thanks the domain functions has helped to construct this report accurately and with quality performance.

Problems
I want now to have the final total for all salesmen, but if I reference the subtotal per each salesman, it only picks the value for the first salesman throughout, that is why I want to reference the primary which is based from the salesmen table and visible on the report. On the report I’m using this code below onload event:
VBA Code

Me.txtsalestotal = Me.finalTotal

Open in new window

I'm putting the code above behind the report , but it keeps on picking the same value for one salesman to all salesmen, that i why a primary key referencing is required here.

I think if I can be helped to know how to reference the primary key ( which is Visible on the report) on the above code, then it will sort out this problem for good.

10 Comments

Hi Experts,

I am attaching details of an issue I am having with using the Foirmat Function in Access 2016 Runtime.

Although one of the queries provides a workable solution it doesn't provide the desired display with the Team Name Left Zero filled.

Could you please take a look at this issue and advise if there is a way to display the Team Id at the left end of the display with leading zeros?

Thanks,
Bob C.
2019-11-17---Access-2016-Runtime-Fo.docx

23 Comments

I'm running MS Access 2016, and I'm looking for a function that will do the following when closing a form in my database.

1. After active form closes, check if there are any other open forms
2. If there are no other open forms, fire a command such as

MsgBox "All forms have been closed."

Open in new window

I appreciate any expert advice you can provide! =)

3 Comments

I'm trying to figure out how to prevent data entry in a sub-form where Date, Time, and Doctor already exists.

The user tries to select a Doctor from a combobox and if that combination already exists give the user a warning message that they have already scheduled that doctor for that date and time.

The field names are Date, Time, and Doctor. The table the subform is bound to is tblPotenialAppointmentDatesAndTimes. The field names in the table are AppointmentDate, AppointmentTime, and DoctorName. Can someone help?

Microsoft Access

17 Comments

Hi

I have an Access form with a combobox that has a button next to it where the user can add
more information to a table that is the data source of that combobox.When the user closes the form
that was opened with the button I want the combobox to automatically be refreshed with the additional data
Do I requery the combobox on the other form? or do I use some way to requery the combobox when the user goes back to it with their mouse?

9 Comments

item not in list error for a numeric value
I HAVE A DROPDOWN BOX.

The following code works fine but if someone accidentally types in just a "numeric" (5) AND hits enter an error occurs.
-2473
Out of stack space

Dim str As String
Dim errYes As Boolean
    
errYes = False
NewData = RemoveSpaces(NewData)
str = Trim(NewData)


If errYes = False Then
   If Len(str) >= 5 Then
      errYes = True
      MsgBox "You Have Copied Data On Top Of Other Data Or The Track Code Is Not In The List. Please Double Check Your Entry."
      Response = acDataErrContinue
      Exit Sub
   End If
End If

Me.cbo_Track_Code.Text = str
Response = acDataErrContinue

Open in new window

Is there a way to amend this code or trap the error and resume ?

Thanks
ordraiders

7 Comments

I am using the File Copy to deploy upgrades to an application, and I am trying to catch instances where the copy fails because the User has the application open. I am trying to use this code to capture the failure:

FileCopy strFileCopyFrom, strFileCopyTo
If Err <> 0 Then

The Err is not capturing the error. I am resetting it to 0 before each copy attempt.

Is there a better method for catching the instances where the Copy fails?

Microsoft Access

10 Comments

Sorry again people there!

I'm now struggling to to put the print view back and be able to close the reports , I'm trying to use the two codes below:

<group id="grpPrint" label="Print" visible="true">
                    <button id="PrntRpt" size="large" label="Print" imageMso="PrintDialogAccess" onAction="=PrintDialog()"/>
                </group>
 
                <group id="grpClgrpRprt2" label="Close">
                     <button id="PPrClose"  label="Close" imageMso="PrintPreviewClose" size="large" onAction="OnCloseReport"/>
                </group>

Open in new window

The reasons why I want to use this method is because Ms Access application does allow users to export report to excel if the USER DOES NOT HAVE full version Ms access , this happen for those using runtime

The export function is working well now runtime but the two are give an error as MACROS FUNCTION does not exist.

Regards

Chris

Microsoft Access

1 Comment

My Access (Office 365) development has a customised application ribbon for switching between forms. All well and good. Thought I had everything tied down, hiding the navigation bar and switching off full menus, disabling the bypass key etc.
What worries me is the 'Tell me what you want to do' box which sits obstinately to the right of the main application tab. Yesterday I typed 'query' in the aforementioned box and guess what, the query designer popped up listing all the underlying tables, inviting me (or any user) to go ahead and mess around with the data without going through the UI.
So, apparently, unless I ditch the ribbon and go back to the time-honoured switchboard menu, the application will always be in a vulnerable state.

Are there any good solutions for this without having to ditch the ribbon (which I spent quite a long time working on)?

Microsoft Access

31 Comments

Hi

We need to calculate the final payroll Journal, unfortunately the figures for PAYE has to come from the extensive and complicate cumulative basis calculations. Now all I want here is to find out the following:

(1)      How to put the Salary journal report consolidating all the PAYE by staff in the report footer
(2)      Hide the detail part which shows calculation by staff, so that when printing only the staff on the report footer should show.
(3)      The details part of the report will have all the calculations for the staff to supply the footer report or act as the recordset but must be hidden so that only the report footer shows only.

Regards

Chris

4 Comments

Hi

I can't see a way to change the form caption shown in the following image. Thanks

Microsoft Access

2 Comments

Hi,
I would like to put custom tool bar in my Ms-Access application. Not the predefined tool bar, but one that I can customize for an accounting application I am doing:
Something like this:

SALES PURCHASES RECEIPTS PAYMENTS
Cash Sales Local Purch. Cash Rec. Cash Payments
Credit Sales Foreign Purch. Linx Rec. Cheque Payments
Other Sales

The titles will be the headings... and the items below will be drop-down list that will appear when click on the heading.
Thank you.

4 Comments

He attached article outlines an issue I’ve encountered with numerous MS Access databases that I own. The databases are all split and shared by multiple simultaneous users. Both the frontend and backend for each database is stored on a network drive.

The article provides workarounds for single-file databases and databases that are split, but in the scenario they lay out for split databases, each user has their own copy of the frontend.

In a situation like mine where the frontend is stored on a network drive and shared, would leasing need to be disabled for the folder that houses the shared frontend as well? Thank you.

https://support.office.com/en-us/article/access-reports-that-databases-are-in-an-inconsistent-state-%EF%BB%BF-7ec975da-f7a9-4414-a306-d3a7c422dc1d

5 Comments

Is there a way to conditionally format a whole row on a datasheet where "Tech" is the value of a field named TypeOfAppt? I want to make the back color yellow if TypeOfAppt is "Tech".

Microsoft Access

12 Comments

I am setting up a procedure to email notifications in my Access 2013 application.

The client wants to send a list of pending payment to each of their shippers. There will be many shippers and each shipper will get and email containing only their pending payments.

I have the logic set up using sendobject and it works just like it was designed to.

DoCmd.SendObject acSendReport, _
                 emailRpt, _
                 acFormatRTF, _
                 eMailTo, _
                 eMailCC, _
                 eMailBCC, _
                 eMailSubject, _
                 eMailBody, _
                 False

Open in new window

The issue is that every time an email is generated for a shipper this message appears on the screen and the user has to click 'Allow'.

This get very repetitive and time consuming when there are dozens of emails being generated.

Is there any way to keep this message from coming up for each email?

Microsoft Access

8 Comments

Latest Windows Security Patch Crashes Microsoft Access 2013/2016 applications with "The OpenForm action was canceled". This started happening Nov 12 2019. I have had at least 14 client computers over the last 2 days call with this issue. So far my only solution has been to do a system restore and pause their Windows updates for the time being.
It also appears that customers with Windows 1809, end of Service was scheduled for Nov 12 2019. and apparently some older Home versions would not allow you to pause updates.
I found an article that suggested that registry settings for MSCOMCTL.OCX are messed up by the update with recommendation to unregister and re-register the control:
using Windows Power Shell: regsvr32 -u "C:\Windows\SysWOW64\MSCOMCTL.OCX" and then regsvr32 "C:\Windows\SysWOW64\MSCOMCTL.OCX"
Although the commands are successful, that does not solve my problem. This problem is occurring for both Windows 10 and Windows 7 computers (I would assume 8.1 as well)

4 Comments

Hi Experts,
I have a question about query. it was just happened. I was trying to run an update query and this query I have been using for a long time and never have any problems but today when I try to update a record#, it gave me an error message "Query " is corrupt" when I click help, it says "You have referenced an undefined query name, check the queries listed in the QueryDefs collection to make sure you reference a valid query name". I even create a new update query, it gave me the same error. does anyone know why and how to fix?

Thanks

Microsoft Access

4 Comments

How would I query my serviceperiod field for an annual period when the dates in the field reflect some quarterly date ranges as such...Thanks for your support

1/1/19 - 3/31/19
4/1/19 - 6/31/19
4/1/2019-4/30/2019 No spaces which was incorrectly entered
5/21/19
etc....???

Microsoft Access

6 Comments

I have a following string data:
[aaaa][bbbb]ccccccccc
or
[aa][bb][cc]dddddd
or
[a][b][c][d]eeeee

I would like to extract ccccccccc and dddddd and eeeee
do you know how to do with single query?

Microsoft Access

10 Comments

I have Excel file, contain multi line data
EESample.xlsx
Database2.accdb
I would like to import the data to access, but it gets a single line.
Do you know how to keep the format in Access after importing?

Microsoft Access

12 Comments

Dear expert team

I I have two date field, one is registration date and one is exam_date. I need to fill data in a field called work_date field as follow:

1) If registration date = exam date, then Exam date to be entered in work date as date value

2) if exam date is next day from registration date, then put registration date in work date

3) if registration date is same as exam date, but between the hours 12 am to 4:am, then put the date before registration date

4) if exam date more than one day off, follow same system above

Please help, I attached the database
Database2.accdb

7 Comments

I am modifying a procedure for a custom Query By Form. The original procedure searches multiple fields in a table, but I am modifying it to search only single field in a table

The modified code is in the click event of the Search - Test 1 button on frmMainMenu. You can view the original unmodified procedure in the click event of the Search - Test 1 (All Orig. Code).

Here are the text box names:

txtDateFrom -- Date From
txtDateTo -- Date To

Here is the modified code:

' First, validate the dates
    ' If there's something in Date From
    If Not IsNothing(Me.txtDateFrom) Then
        ' First, make sure it's a valid date
        If Not IsDate(Format(Me.txtDateFrom, "mm/dd/yyyy")) Then
            ' Nope, warn them and bail
            MsgBox "The value in Contact From is not a valid date.", vbCritical, gstrAppTitle
            Exit Sub
        End If
        ' Now see if they specified a "to" date
        If Not IsNothing(Me.txtDateTo) Then
            ' First, make sure it's a valid date
            If Not IsDate(Format(Me.txtDateTo, "mm/dd/yyyy")) Then
                ' Nope, warn them and bail
                MsgBox "The value in Contact To is not a valid date.", vbCritical, gstrAppTitle
                Exit Sub
            End If
            ' Got two dates, now make sure "to" is >= "from"
            If Format(Me.txtDateTo, "mm/dd/yyyy") < _
                Format(Me.txtDateFrom, "mm/dd/yyyy") Then
                MsgBox "Contact To date must be greater

Open in new window

…

13 Comments

Need help with overcoming 3035- system resources exceeded error carrying out an INSERT sql statement.

Please note image attachment that shows error message - (along with value of what sEntireLineItem is) and following code:
The yellow highlight should be on Currentdb.Execute sSQL

      sSQL = "INSERT INTO [usgaapfileswtags] ([tagID], [usgaapfilesid], [usgaaprow], [usgaaplineno], [usgaapvalid]) VALUES (" & lTagID & ", " & lIDFileName & ", '" & sEntireLineItem & "', " & lCounterTag & ", " & bValidTag & ")"
      CurrentDb.Execute sSQL
      DoEvents

Open in new window

So obviously the sql INSERT statement will be pretty long

I already do this earlier in the code:

If Len(sEntireLineItem) > 65500 Then
        sEntireLineItem = Left(sEntireLineItem, 65500)
        
      End If

Open in new window

And I do have usgaaprow as Long Text as a data type. I did check all the other values are quite small and match without a problem. I know it's the variable: sEntireLineItem

Here is the value of sEntireLineItem string itself:

  <us-gaap:BusinessCombinationDisclosureTextBlock contextRef=''FD2018Q4YTD'' id=''Fact-82E23980F0AD591486D298F5A3EA3736''>&lt;div style=''font-family:Times New Roman;font-size:10pt;''&gt;&lt;div style=''line-height:120%;padding-top:18px;font-size:10pt;''&gt;&lt;font style=''font-family:inherit;font-size:10pt;font-weight:bold;''&gt;ACQUISITIONS, GOODWILL, AND ACQUIRED INTANGIBLE ASSETS&lt;/font&gt;&lt;/div&gt;&lt;div

Open in new window

…

9 Comments

Microsoft Access

Related Topics

Microsoft Access

Related Topics