We help IT Professionals succeed at work.

Microsoft Access

222K

Solutions

52K

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.

I'm trying to figure out how to open the navigation pane via VBA code after a password has been entered.  Here is what I have so far...

    Dim strName As String
    strName = InputBox("Please enter the database password.", "VBA InputBox Function")
    If strName = Empty Then Exit Sub
    If strName <> DLookup("[DBpassword]", "tblDBPassword") Then
        MsgBox "You do not have access to this function"
        Exit Sub
    End If

    'Code here to open the navigation pane...
0
Hi

I copied the buttons in the image from another form to this form and pasted them on one tab. They now appear on all tabs. I want one of the tabs to not show the buttons. How do I achieve this? Thanks

1
0
Experts,

How can I modify this in a textbox on a form:
=Sum([tblPayments.Amount])

To only sum if NotPaidYN = False
In other words, I only need to sum the tblPayments.amount if NotPaidYN = False


thank you
0
We have finally configured our general sales accounting in Ms Access VBA with the serial port gadget by ensuring that the data received from the gadget confirming the signing of sales invoices is  saved in a note pad text file. Then use that saved to update the invoice table manually with the help of an update form on the screen shoot below:

EsdInvoicessUpadate.png

Initially we wanted that data that is saved in the note pad attached below to be saved into the invoice table automatically , now because of the hex characters which I have failed to remove at response time so that only the Json format remain has rendered the Json parsing impossible:


The original VBA code that does the job is below, the problem comes with the actual POS , the current method can make the people wait in ques for too ,if we are to implement the current system of imputing the five mandatory parameters manually:

(1) ESD Time
(2) TerminalID
(3) InvoiceCode
(4) InvoiceNumber
(5) Fiscal Code

Private Sub CmdPosJsons_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim fld As DAO.Field
    Dim qdf As DAO.QueryDef
    Dim prm As DAO.Parameter
    Dim root As Dictionary
    Dim transaction As Dictionary
    Dim transactions As Collection
    Dim item As Dictionary
    Dim items As Collection
    Dim Tax As Collection
    Dim i As Long
    Dim Z As Integer
    Dim j As Long
    Dim t As Long
    Dim itemCount As Long
    Dim taxCount As Long
    Dim strTaxes As 

Open in new window

0
Hi Experts,
I have a question about the calculation time problem in Access Database for Automatically close the database if the database is idle for a period of time.
How do I calaulate for hour(s)?  I would like to set to if idle for 3 hours then close the database automaticaly.

Is this correct for Calculate mintue? -- > ExpiredMinutes = (ExpiredTime / 1000) / 60    

here is my code in the calculation part:

'I set the timer Interval to 1000
ExpiredTime = ExpiredTime + Me.TimerInterval


    ExpiredMinutes = (ExpiredTime / 1000) ' this is for 1 second
    Me.txtIdleTime = ExpiredMinutes
   
If ExpiredMinutes >= 10 Then  ' this is for 10 minutes
   ExpiredTime = 0
   Application.Quit acQuitSaveAll
End If

Thanks,
0
Experts,

How can I format this field in the query design window?
It doesnt seem to want to hold the currency format.
You can see below that I have selected currency but it doesnt format.



Currency
Currency2
0
I have a form and a subform.  I have the form set to maximize onopen.  But no matter what I try I can't get the subform to maximize onopen.  What am I doing wrong?
0
Running WinServ2012R2 with Access and Outlook 2016.

Using job scheduler and macros/VBA to send out automated reports via email.  Messages are going out fine....but now I want to see who's actually reading them and who's ignoring them.

Outlook profile on the machine has read-receipt-requested as a default.  Access is sending the messages out via EmailDatabaseObject and/or SendObject....and it's not picking up that profile setting.  Instead, the sent messages have the tracking options (delivery/read receipt) greyed out.

Any way to fix/enable it?  If not, we'll live....but it would have been nice.  

The Access/Outlook "break" might even be by design.  I noticed that Access macros will still run even if Outlook is NOT open.  The messages will just queue up in the Outbox and will go out whenever Outlook gets re-opened.
0
Is it possible to Trim the first 4 characters & the second last from the Json string below, the idea here is to remain with pure json, so that if I fail to iterate it , then I can have it printed and posted to the table manually.(Characters not required are  ]    j  and 4c)
Below is the Ms Access VBA code that takes it to text file , but not fully cleaned up.

Text file Export VBA

n = FreeFile()
    Open "C:\Users\chris.hankwembo\Desktop\Leader\test.txt" For Output As #n
    Print #n, strData
    Close #n

Open in new window

Json String

 ]    j{
   "PosVendor": "Nector Prime Accounting Solutions",
   "PosSoftwareVersion": "2.0.0.1",
   "PosModel": "Cap-2017",
   "PosSerialNumber": "100100001829",
   "IssueTime": "20200326115321",
   "TransactionType": 0,
   "PaymentMode": 0,
   "SaleType": 0,
   "LocalPurchaseOrder": "",
   "Cashier": "Admin Manager",
   "BuyerTPIN": "",
   "BuyerName": "",
   "BuyerTaxAccountName": "",
   "BuyerAddress": "",
   "BuyerTel": "",
   "OriginalInvoiceCode": "",
   "OriginalInvoiceNumber": "",
   "Memo": "",
   "Items": [
      {
         "ItemId": 1,
         "Description": "Cleaning Materials",
         "BarCode": 19,
         "Quantity": 1,
         "UnitPrice": 56,
         "Discount": 0,
         "TaxLabels": [
            "A"
         ],
         "TotalAmount": 64.96,
         "IsTaxInclusive": true,
         "RRP": 0
      }
   ]
}4c

Open in new window

0
Hi Experts,

I am trying to create a view and getting datatype mismatch

V_Screening_View
SELECT CStr("-" & Mid([Caregivers_CaregiverCode],InStr([Caregivers_CaregiverCode],"-")+1)) AS CG_Code, InStr([Caregivers_CaregiverCode],"-") AS 1, V_Screening_Covid.*
FROM V_Screening_Covid
WHERE (((InStr([Caregivers_CaregiverCode],"-"))<>0));

Open in new window

VisitReport_View
SELECT "-" & CStr(Replace(Mid([Caregiver],InStr([caregiver],"(")+1),")","")) AS CG_Code, [Visit Report].*
FROM [Visit Report]
WHERE ((([Visit Report].Caregiver) Is Not Null));

Open in new window


Now this one is giving me the error

SELECT VisitReport_View.*
FROM V_Screening_View RIGHT JOIN VisitReport_View ON V_Screening_View.CG_Code = VisitReport_View.CG_Code
WHERE (((V_Screening_View.CG_Code) Is Null));

Open in new window


Any idea?

Thanks
0
I need to implement or add a sleep sub routine in Ms Access between the commwrite & commread so that I'm sure that all the data received from the gadget is read and received into my database. Now below is my draft sub routine see how to improve it. The gadget writes all the invoices without problems see attached screen, the only issue here is receiving that why the suggestion for a sleep sub:

Dim strData  as string
Dim strDataComplete as string

        strDataComplete = ""
        Do While True ' Loop until serial port is read
            Sleep 1000
            lngStatus = CommRead(intPortID, strData, 100000) 'For 100000 is the max data length you want read, change to suite
            Sleep 1000
                            
            If lngStatus = 0 Then
               'Keep polling to see if serial has data
            ElseIf lngStatus <> 0 Then
                strDataComplete =  strData 'Put data pulled from serial port here 
            End If
                    
            If lngStatus = 0 And strDataComplete <> "" Then
                Exit Do    ' Exit loop when all data is read
            End If
            
        Loop 

Open in new window



parsing-Json.png
0
Hi,
I have 2 textboxes in main form.

what i want is:

Every time i write in textbox 1, I want textbox 2 to get a NULL value and vice versa.
The action I did is via the IF command, only the problem is that textbox 2 is getting the value NULL, but it doesnt displayed in the form (not refreshed).
How can I refresh textbox 2 without saving to form.
0
I have a form that has 3 multi-select listboxes on it.  Is there a way to filter the records in the sub-form on the same form when the user has selected records in the listboxes?
0
I have a spreadsheet - which is constantly defaulting back to Manual Calculation - which is irritating to say the least. I got it from someone else so not sure if they had turned off Automatic Calculation. I checked my own options and they are fine - how do I make sure it doesn't keep going back to Manual.
0
display a prior month value in a query for a field.
In  this  Table:   dbo_QCS_CRS_SCORE
Example data:
JANUARY =  33.4
FEBRUARY =  98.2
MARCH  =    96.3


I have the fields JANUARY TO DECEMBER AS FIELD NAMES. IN "dbo_QCS_CRS_SCORE"
i WOULD LIKE TO ADD THIS TO THE QUERY BELOW AND SHOW THE  PRIOR MONTH SCORE.

so say this is March I need to display February field.
When April 1st is here. I need to display March field ?


SELECT [EMPLOYEE_FIRST_NAME] & " " & [EMPLOYEE_LAST_NAME] AS NAME, QCS_CRS_EMPLOYEE_DIRECTORY.user_id, QCS_CRS_EMPLOYEE_DIRECTORY.EMPLOYEE_STATUS, dbo_QCS_CRS_SCORE.YTD_AVG
FROM QCS_CRS_EMPLOYEE_DIRECTORY INNER JOIN dbo_QCS_CRS_SCORE ON QCS_CRS_EMPLOYEE_DIRECTORY.user_id = dbo_QCS_CRS_SCORE.RACFID
WHERE (((QCS_CRS_EMPLOYEE_DIRECTORY.TITLE_DESCRIPTION) In ('On-Site Specialist','Desktop Specialist','Reference Analyst')))
ORDER BY [EMPLOYEE_FIRST_NAME] & " " & [EMPLOYEE_LAST_NAME];

Open in new window



Thanks
fordraiders
0
Hello @Aikimark  

You  helped me with a solution earlier this month that I am now using on 'real' data and getting some odd results.  

When running q_eeSolution2, records 4132-4150 are included with the result and I can't determine why.

In addition, I believe your routine ignores any leading or trailing [spaces] I have included on some of the codes. For example, Some of the 'codes' are only 2 characters (SA, AB, AG, etc.), which I have entered as [space]'code'[space] in order not to capture all record where the business name may include that 2-character combination - which is (many) thousands of records. Is there a way to capture the 'code' exactly as it is entered in the table?

Thank you.
EE_Sample.accdb
0
I have a table called:
Rebate_Qualifier
3 fields:
Rebate_ID      Entity_Value      Qualifier_ID

"Entity_Value" will always have a value in the field.

I have 2 variables    rb   and   ent

rb = SLS-000118-MAIN-A1-5-R1
ent = 5

The Rebate_Id is null
Qualifier_ID is null

Table example below:
Rebate_ID	Entity_Value	Qualifier_ID
	                AABAA	
	                AABAA	
	                AABAA	
	                AABAA	

Open in new window


What I need:
I need to update the Rebate_Id field with  variable  rb
I need to update the Qualifier_ID field with  variable  ent


Thanks
fordraiders
0
I have a form with several checkboxes.  How can I save the record when a checkbox is either checked or unchecked?
0
Does anyone know what happens when you re-enable SMBLeasing. It was a fix to stop back end databases going into an inconsistent state when used with Windows 10 and office 365 but apparently it can cause network speed issues. I have a client that can only use one front end out of three. One works perfect but reduces the access speed down to intolerable levels on the other two. I have applied every other fix I can see but problem persists. I just wondered if the fix below would still need the SMBLeasing turned off?

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanWorkstation\Parameters]
"FileInfoCacheLifetime"=dword:00000000
"FileNotFoundCacheLifetime"=dword:00000000
"DirectoryCacheLifetime"=dword:00000000
0
adding a new number sequence to an existing alphanumeric sequence.

I have a dropdown with alphanumeric sequence that either ends in  R1 OR R2 OR R3  OR R4  etc..

and/or  also    I1  , I2 , I3 , I4 etc...

SLS-00612-MAIN-I1
or
SLS-0022-MAIN-R2

What I need:

When i press to save a record i need to automatically come up with the next last 2 alphacharacters in the sequence.

i.e.
So i make a choice from my combo box and the selection is
SLS-023-MAINROOM-R2

The new number is going to be :
SLS-023-MAINROOM-R3

i.e.
So i make a choice from my combo box and the selection is
SLS-006627-MAIN-I1

The new number is going to be :
SLS-006627-MAIN-I2

Thanks
fordraiders
0
Experts,

I am trying to combine the below in an unbound field on a report.
Maybe its better to do it by VBA?  
Not sure which way is easier.

thank you...

In English:
If tblLetterOfCredit.PaidInFullYN = True then "Paid In Full", if not then Dsum but only if tblPayments.PaymentDate is not null (meaning there is a date entered)

--------------------------------------
If tblLetterOfCredit.PaidInFullYN = True then "Paid in Full"
Else:

If is not null [PaymentDate] then Dsum [Amount].  How to add Is Not Nulll to below?
=iif(DLookUp("[PaymentDate]","tblPayments","[LCID] = " & [LetterOfCreditID]
dsum("[Amount]","tblPayments","[LCID] = " & [LetterOfCreditID]
0
I'm looking for help separating text from numbers in an Access string. Example: "BURLINGTON TOWERS1000 BURLINGTON AVE N 603"

I need everything left of 1000 in it's own field.

There are a few thousand records with every combination of text and numbers in this table with no space between than.
0
In the light of no progress from MS about access windows 10 O365  bug I'm attempting a sequel server option.

Pretty weird how the simple updater now renames every key field, (is this normal?) meaning I have to go into 60 tables and rename the linking field so that any reference any of them in a form still works.

Just wondered, as my 30,000 lines of code are all access VBA, is it going to take forever to make something work?

Am I being pessimistic or realistic?

Any tips?
0
Hi

I am using Microsoft Access.  I am using a SDK to print directly to a Brother labeller  (one of 3 templates)

In this case, the label will only have one field.   My code will populate that field.

In order for my app to choose the appropriate  label template,  I must first calculate the printed width of my data and then choose the appropriate template.

So, how do I calculate the width of that printed text in mm (if possible) ?

ps.  I know that  many of you are helping others,  just because..       I hope your good Karma comes back to you and your loved ones in these difficult times.
0
Hi Experts,
I knew I asked this question but now i'm having a problem with send email from Access 2016 from office 365 outlook email.
here is my code:

     Private Sub cmdSendEmail_Click()  
      Dim oApp As Object
      Dim oEmail As Object

      Set oApp = CreateObject("Outlook.Application")
      Set oEmail = oApp.CreateItem(0)

     With oEmail
      .To = [lastname] & " " & [firstname] & " <" & [Email] & ">"
      .display
     End With
  End Sub

This code works on the regular outlook email but it's not working on the outlook in office 365.  I test this code in my personal outlook email and it works fine but it doesn't work in my work email which we're using office 365.  Are they different?  any ideas I can make it works with office 365 outlook?

Thanks
0

Microsoft Access

222K

Solutions

52K

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.