Microsoft Access Help

I have access o365
I have a really weird problem.

When i put a form in design mode.
The cursor keeps spinning for several seconds.
Quits
and then spins again.
it repeats about five times.

Then when i try to click on any part of the form.
It starts all over again ?

Any suggestions
fordraiders

4 Comments

I'm getting an "Object variable or With block variable not set" error with the following code:

    Dim objOutlook As Object
    Dim objOutlookEmail As Object
    Dim rstEMail As DAO.Recordset
    Dim strEmail As String
    Dim strEmailBody As String
    Dim strEmailCC As String
    Dim strEmailBCC As String
    Dim strEmailSubject As String
    Dim strEmailTo As String
    Dim strFileName As String
    Dim strReportName As String
    
    Dim ContactEmail As String
    
    Set objOutlook = GetOutlookObject()
    
    strEmailBody = "Attached please find your letter."
    strEmailBCC = Nz(DLookup("[SalesRepEmailAddress]", "tblSalesRepsLU", "[SalesRepID] = " & [Forms]![frmEstimates]![txtSalesRep]))
    strEmailSubject = "Quote #" & Forms!frmEstimates.txtEstimateID & " " & Forms!frmEstimates.txtRevDateAndNote & " - " & Forms!frmEstimates.txtJobTitle
    strReportName = "Quote Letter"
    strFileName = Environ("Temp") & "\" & strReportName & " # " & Forms!frmEstimates.txtEstimateID & ".PDF"

    DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, strFileName
        
        strEmailTo = Forms!frmEstimates.txtContactEmail
        
        Set objOutlookEmail = objOutlook.CreateItem(olMailItem)
        With objOutlookEmail
            .To = strEmailTo
'            .CC = strEmailCC
            .BCC = strEmailBCC
            .Subject = strEmailSubject
            .Attachments.Add strFileName
            .HTMLBody = strEmailBody
            .Display
        End With

Open in new window

…

Microsoft Access

6 Comments

Using MS Access 2013, is there a way to toggle "Compact on Close" using VBA at runtime? The reason, I don't want it turned on in the development or testing version which are shared. I want to turn in on automatically in the production copy which is deployed locally on the workstation.

4 Comments

access o365
subform not displaying all records in a passthrough query ?

i have a subform that has the recordsource set to a pass through query.
when i execute the passthrough query the subform is only displaying 50 records ?
subform is set to continous form.

sql server shows the dataset containing 24,000 records.

Thanks
fordraiders

never ran across this before ?

2 Comments

I have a simple table with names and count of something by month
ID      ENAME      May      JUNE      JULY      AUG
1      Pete      2            5        4
2      John      5        3
3      Mary      3        3        4        2
4      Sara      5        5
5      Lisa        1        1        4 2
6 Mike 4
for example, for Pete, there is null for the month of August
for Mary, everything is filled up

what I want to count an average by name:
for Mary should be 12/4=3
for Lisa should be 8/4=2
for Sara should be 10/2=5 (only two months, so average should be for two months)
for John should 8/2=4 (only two months, so average should be for two months)
for Mike should be just 4, because the rest are nulls

Microsoft Access

2 Comments

I use a main form (recipe) with a datasheet subform (ingredients).

In the subform, the user can add different ingredients. After each ingredient selected (via combobox) the datasheet is updated for the correct price and quantity. However, I need to add a computed column for PRICE * QTY for each row without this field being stored in the underlying table (unbound). The reason is that the price should be fetched in the ingredients table to ensure that we always use the latest ingredient price. At the moment, I use =DLookUp("Price","Ingredients","Id=" & Nz([cmbIngredient],0))*[xQty]) which works fine. Is there a better way to do it?

Second, I need to display the sum of all ingredient costs ( in the subform) on the main form. What is the best way to do this?

Thank you so much for any help!

Microsoft Access

5 Comments

Access 2019. I am writing an Address Book Database. I know there are many already done but they do not meet what I need. I have tblAddressBook which will contain all of the information. There is also tblChildren which will be a subform to tblAddressBook. Now the issue.

I would like to have a tab control form with two tabs, Personal Contact and Business Contact. When the Personal Contact tab is selected I want to have another tab control with the tabs Personal, Spouse, Children (sub to tblAddressBook) and Category. When the Business Contact is selected I would like to display fields pertaining only for Business contacts. I have attached a photo of a first attempt at this but when I click the Business tab the sub tab for the Personal Information is still there.

10 Comments

Question by

Fed Alexander

2019-08-21Solved

Append Issue

Hello Experts,

I tried to append the data from the excel file to access table and for some reason some o the filed comes blank. however, there is data in the excel file. can I get any help with this?
123456.xlsx

Microsoft Access

6 Comments

LVL 3

Question by

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc

2019-08-21Solved

Subforms

Sorry people its now my problem to reopen this question, I simply tested the code below on single form , but not tested on a subform:

Option Compare Database
Option Explicit
Private Sub CboSales_AfterUpdate()
Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Set db = CurrentDb
strSQL = "SELECT * FROM Qry2 Where Qry2.SalesID = " & Me.CboSales
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
Do Until rs.EOF
Me.[sfrmlinesales Subform].Form!Cost.AddItem rs("Qty")
Me.[sfrmlinesales Subform].Form!Cost.AddItem rs("Cost")
rs.MoveNext
Loop
End Sub

There is now error on the below lines

Me.[sfrmlinesales Subform].Form!Cost.AddItem rs("Qty")
Me.[sfrmlinesales Subform].Form!Cost.AddItem rs("Cost")

It say :

Object doesnt support this property

36 Comments

LVL 6

Question by

bfuchs

2019-08-20SolvedPrivate

Stopping code execution middle of loop.

Hi Experts,

I have the code below running a loop and processing files.
While the program executes files I can stop it middle of execution (Cntrl+Break).
However when there are no files and it keeps looping, the only way I can stop it is by killing the process.
How can I change that?

    Do While Len(StrFile) > 0 And Not IsFileOpen(sDir & StrFile)
''        Do While IsFileOpen(sDir & StrFile)
''            ' do nothing
''        Loop
        If InStr(1, StrFile, "Full") = 0 And InStr(1, StrFile, "Part") = 0 Then
            i = CountOfRecords(sDir, StrFile)
            If i > 1 Then
                'Debug.Print StrFile & " - " & CountOfRecords(sDir, StrFile)
                If InStr(1, StrFile, "PatChanges") > 0 Then
                    strTable = "Patients"
                    l = ImportDataToCaspio(strTable, sDir & StrFile, i)
                ElseIf InStr(1, StrFile, "SchChanges") Then
                    strTable = "Schedule"
                    l = ImportDataToCaspio(strTable, sDir & StrFile, i)
                ElseIf InStr(1, StrFile, "CGChanges") Then
                    strTable = "Caregivers"
                    l = ImportDataToCaspio(strTable, sDir & StrFile, i)
''                ElseIf InStr(1, StrFile, "PatMedProfileChangesV2") Then
''                    strTable = "Patients_Medications"
''                    l = ImportDataToCaspio(strTable, sDir & StrFile, i)
                  Else
                      strTable = ""
                      l = 0

Open in new window

…

30 Comments

LVL 6

Question by

bfuchs

2019-08-20SolvedPrivate

Create a log file.

Hi Experts,

I'm looking for a VBA function that will accept two params and edit a log file.
two params are file path & name, and the value to be inserted.
including an error handler (to ignore, not to stop execution by any means).

Thanks

4 Comments

I have a Save and Create new record button on my form but, I need to ensure all the required fields are not null before the record can be saved and a new record open....This is my starting code:

Private Sub SaveAndCreate_Click()

End Sub

10 Comments

My question is on parameter query in VBA Ms access 2016, the code below works well whenever data is updated , but I want it to show only the related data as per selection

Private Sub Cbostaff_AfterUpdate()
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Qry1", dbOpenDynaset, dbSeeChanges)
Do Until rs.EOF
Me.liststaff.AddItem rs("FirstName")
Me.LsJobtitle.AddItem rs("Jobtitle")
Me.LlastName.AddItem rs("LastName")
rs.MoveNext
Loop
End Sub

Open in new window

I tried to use the WHERE clause it keeps on giving me an error, see if you can spot where to mend it:

Private Sub Cbostaff_AfterUpdate()
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Qry1", dbOpenDynaset, dbSeeChanges) Where Qry1.ID = &Me.Cbostaff
Do Until rs.EOF
Me.liststaff.AddItem rs("FirstName")
Me.LsJobtitle.AddItem rs("Jobtitle")
Me.LlastName.AddItem rs("LastName")
rs.MoveNext
Loop
End Sub

Open in new window

Regards

Chris

6 Comments

More Azure SQL migration issues. :-(

A couple of my Access forms no longer allow additions or edits. They worked find when the BE was on the local SQL Server. They stopped working after migration to Azure SQL.

I don't even know where to start looking for the problem. The login to Azure SQL has dbo rights.

10 Comments

Question by

urjudo

2019-08-19Solved

Module in Access

Hi Experts,
Can someone explain what does this do in a form timer?

Private Sub Form_Timer()
Echo False
DoCmd.Maximize
Sleep 100
Echo True
Me.TimerInterval = 0
End Sub

what happen is I remove the "sleep 100", is going to affect the form?

also what are these functions for, do they need to be in a module of every program or can be omit:
Function apiGetScrollInfo
Function apiSetScrollInfo
Function GetWindowLong
Function SendMessage
Function apiGetClassName
Function apiGetParent
Function apiGetWindowLong
Function apiGetWindow

Thanks

13 Comments

I have MS Access 365 with SQL Server backend using ODBC links. I have the latest ODBC driver (version 17). I am connecting to a corporate resource that assigns SQL view using a Denodo drivers. The link and data flow works great until a new record is added. If new records are added to the main SQL tables, Access returns an error: "ODBC--call failed. Communication error while getting a tuple; Error fetching next row (#26). This pops up if I have the linked table open in Access and Access does an automatic record refresh. Takes usually about 2 minutes for this to pop up. I've noticed that each time I check the main database after receiving this error, there were new records being added. I went into File, Client Settings, Advanced, and set Refresh interval (sec): to 0. I also set ODBC refresh interval (sec): to 0. I also tried these at 2000, but I still seem to get the error. If I have a form open that's linked to the SQL backend, instead of getting an error, Access just crashes. IT Dept. can't see any errors in the Denodo drivers on the backend. Any thoughts on how I can troubleshoot or stop it from erroring-out when new records are getting added?

6 Comments

Question by

leeroypitre

2019-08-19Solved

ACCDE crashes

Using MS Access 2013, I've developed an application that's been running fine for over a year. Once development is completed on changes, I use debug compile to make sure no issues are found. Then I create an ACCDE and push the new release into out testing environment.

Something changed about a week ago. The ACCDB copy run fine. However, now each and every time I create a new ACCDE and try to launch the ACCDE it crashes every time.
I have spent numerous hours researching and have no idea what is causing this. I've attached a screen capture of the MS Access crash window.

VBA compiles cleanly. New ACCDE files from those affected ACCDB files still crash.

The plot thickens. Spent last Thursday & Friday recovering from an older ACCDB that compiles and ACCDE runs. At 7am I stopped, compacted, decompiled, made ACCDE. Runs fine. So made a backup of my ACCDB. At 8am, did same thing. Everything fine. At 10am did same thing, everything fine, made new backup. At 11am packed, decompiled, made ACCDE. Crashes. Copied back my 10am ACCDB. Made ACCDE, crashes. Went to my 8am backup. made new ACCDE, crashes.

I also attached a zipped copy of the error exported from the system event viewer.

This is driving me crazy. I cant work under these conditions. Any help will be greatly appreciated. I'm at a complete stand still until I figure this out.
MS-Access-2013-error-20190816-0734.zip
Capture.JPG

7 Comments

Question by

urjudo

2019-08-19Solved

Query syntax

Hi Experts,
I have a query question. I have a Attorney table that have fields: Last Name, First name, Firm, address etc. I want to have user to be able to search either the attorney's last name or the firm to lookup if this attorney or this firm is existing in the table but I don't want to create two different command buttons for this search, I want to create one command button for this search. How do I do that? I don't want to do an union query because I want users to be able to add or edit in the search form. here is my query:

SELECT tblAttorney.ATYFIRST, tblAttorney.ATYLAST, tblAttorney.ATYFIRM
FROM tblAttorney
WHERE (((tblAttorney.ATYLAST) Like [Please Enter Attorney Last Name] & "*"));

Thanks

8 Comments

I use a login form to access my application. The user needs to enter his login name plus password and then click on ACCEPT button to continue. This works fine. However, it does not work as desired when the user enters his password and presses enter. In this case it should execute the click event but obviously it will just add a LF. Is there a way to prevent the LF and to force the app to recognize this event and execute the ACCEPT button event.

Microsoft Access

6 Comments

ms app that displays a pdf file in an access sub form. For years had been using an adobe activex
add-in to run this by providing a pdf file path as the control source. This has been redacted or never legit in the first place after 2010 version. Now I have to use a MS web browser control in place of the adobe activex control. The app let's the user update info in another sub form and then "Refresh" the pdf by recreating the file. During this process, I'm getting rt error 2501 the outputto operation has been cancelled when I try to refresh. It appears that adobe reader is still running in the background, which I think is causing the error.
I'm looking for a code snippet to close the adobe reader app.

The vba in question is
DoCmd.OpenReport "Work Order Report", acViewReport, , "JOB.JOB_NO='" & CurJobNo & "'" ', acHidden
DoCmd.OutputTo acOutputReport, "Work Order Report", acFormatPDF, Temp1Path, False
DoCmd.Close acReport, "Work Order Report"

the docmd.Outputto.. item is where it fails

7 Comments

The code below is not populating the record into the subform from a query it says too few parameters , this is supposed to be a simple thing its now frustrating, kindly see below:

Private Sub CmdGetGrns_Click()
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("QryPurchasesOrderFin", dbOpenDynaset, dbSeeChanges)
Do Until rs.EOF
Forms![frmGrn].Form![sfrmGrnDetails Subform]![PurchasesID].AddItem rs("PurchasesID")
Forms![frmGrn].Form![sfrmGrnDetails Subform]![ProductID].AddItem rs("ProductId")
Forms![frmGrn].Form![sfrmGrnDetails Subform]![Qty].AddItem rs("Quantity")
Forms![frmGrn].Form![sfrmGrnDetails Subform]![Cost].AddItem rs("CostValue")
Loop
End Sub

Open in new window

The query where the data is suppose to come from

SELECT tblPurchasesHeader.PurchaseID, tblPurchasesDetailslines.ProductID, tblPurchasesDetailslines.Quantity, tblPurchasesDetailslines.CostValue
FROM tblPurchasesHeader INNER JOIN tblPurchasesDetailslines ON tblPurchasesHeader.PurchaseID = tblPurchasesDetailslines.PurchaseID
WHERE (((tblPurchasesHeader.PurchaseID)=[Forms]![frmGrn]![CboOrder]));

Open in new window

See if you can see the problem here!

Regards

Chris

Microsoft Access

2 Comments

I have imported a excel file into ms access table i am looking for the fastest way to loop through the records to update certain fields.
I am looking for a procedure that uses an array and one that doesn't. If that possible.
Thanks

Microsoft Access

6 Comments

Hello Team,

I am trying to calculate Min, Avg, Max, Standard deviation and count based on specific criteria. I want to calculate based on number of shifts and based on titles. this will need to be by month, July, August, Sept and by year.

one staff may have worked 4 shifts per month, then went on vacation. if he cared for 100 customer during the 4 days, max would be 100, average would be 25...etc. I want also to count the number of shifts he worked, and the number of customer he cared for. the result needed is by title also. I used the query below and I got a strange results.
Database44.accdb

12 Comments

Hi

I am looking for Access VBA code to apply conditional formatting on certain columns on excel work sheet

1. if Column B(date) is null then I need to apply red color on those null cells on column B
2. if column B(date) is not null and if corresponding value on column A is less than today() then color Amber on those cell on column A

Public Sub CondinlFormat()
Dim ObjExcel
   

    Set ObjExcel = CreateObject("Excel.Application")
    ObjExcel.Visible = False
    ObjExcel.Workbooks.Open "C:\aa\abcd.xlsx"

    Set Objsheet = ObjExcel.ActiveWorkbook.Worksheets(3)
    Objsheet.Activate

                 
        If Objsheet.Range("B1:B999") = 0 Then
        Objsheet.Interior.Color = RGB(255, 100, 0) 'Red
        End If
        
    ObjExcel.ActiveWorkbook.Save
    ObjExcel.ActiveWorkbook.Close
    ObjExcel.Quit
    
    Set ObjExcel = Nothing
    Set Objsheet = Nothing
End Sub

Open in new window

Thank you
A

9 Comments

I need to query a table to get the total sales by a supplier per state. The field names are:

OrderNumber
OrderDate
ShipState
TotalSale
SupplierID
SupplierName

How do I do that?

5 Comments

Microsoft Access

Related Topics

Microsoft Access

Related Topics