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 the following VB command:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "q_testquery", "strFilePathName", True

Is there a way if "q_testquery" returns no records still have an excel document generated with "No Records Found"?
0
How do you change the sort order of a combo box when you add items to it in Access 2010?
0
Hi Experts,
I have a question, is anyway to change the username from Jane.Doe to Doe, Jane?
The Setup now is User= Environ("UserName"), it shows : Jane.Doe and I would like to show on the report as Doe, Jane

many thanks,
0
Hi Experts!

I am looking at alternatives to Docusign for a client. They have an Access application which they use to generate sales contracts and would like to be able to integrate/automate the process. They currently use Docusign but it is not integrated and requires quite a few extra steps for each contract. The pricing for Docusign is prohibitive based on their volume - so they have asked me to look into other options.

My question is: Has anyone used other e-signing tools and integrated them with an Access application (VBA, or API's, or whatever)?

I'm told their contract with Docusign is coming to an end soon so this is pretty hot at the moment.

Any help or suggestions will be greatly appreciated!
0
I'm using the following code to download files from the internet and save to a location on my computer. I need to close the current browser window after the download (not the application). Thanks


Dim myURL As String

myURL = "https://portal.kkk.kkknet/wps/portal/unauthportal/home/"


Dim HttpReq As Object
Set HttpReq = CreateObject("Microsoft.XMLHTTP")
HttpReq.Open "GET", myURL, False, "zzzz", "zzzz"
HttpReq.send

ShellEx "https://portal.kkk.kkknet/ibmcognos/bi/?pathRef=.public_folders%2FMAS%2FALJ+HQ+Ad+Hoc+Reports%2FPerformance+Goals%FFY19+Aged+Case+Goal+List&format=spreadsheetML&Download=true&prompt=true"


myURL = HttpReq.responseBody
If HttpReq.Status = 200 Then
    Set oStrm = CreateObject("ADODB.Stream")
    oStrm.Open
    oStrm.Type = 1
    oStrm.Write HttpReq.responseBody
    oStrm.SaveToFile CurrentProject.Path & "\" & "FY19 Aged Case Goal List.xlsx", 2 ' 1 = no overwrite, 2 = overwrite
    oStrm.Close


    
End If

Open in new window

0
In MS Access 2013 front end back end on SQL Server 2014 I have several forms on which I do the Search and pull records on form (not just for edit) it could be just search and show data.
Is there way to record those search occurrences:  Who did that  and What was search statement? I am not interested in log of changes of record, delete or adding new ones but only in search.
0
Hi, can any show me how to apply the following vba syntax to open another secured Access DB2 from DB1's Form Button click?
Both DB are running Access 2003 Runtime and configured with the same workgroup mdw, so that users do not need to retype their user name and pw again when DB2 is opened.

Desktop Icon Click is "C:\Program Files (x86)\Microsoft Office\OFFICE11\MSACCESS.EXE" "\\Server\DBFiles\DB2.mde" /wrkgrp "\\Server\DBFiles\Security.mdw" /runtime

DB1FormButton Click()
Dim strMdb As String
 strMdb = "\\Server\DBFiles\DB2.mde "
 Dim appSecondInstance As New Access.Application
 With appSecondInstance
    .Visible = True
    .UserControl = True
    .OpenCurrentDatabase strMdb
    .DoCmd.OpenForm "Applicant Details", acNormal
    .Forms("ClientArchive").Filter = "ClientID Like '*" & Me.Searchinfo & "*'"  
    .Forms("ClientArchive").FilterOn = True
   
 End With
0
Kindly correct me if I’m wrong on the Less DSN File:
(1)      Once the ODBC string is done and tested from the developmental computer, that ODBC less DSN File will move with the application where it goes, the only thing to be done is install the correct ODBC version on each work station AND install the database on central place while the work station will either have full Ms Access or Runtime + ODBC.
Questions
(1)      What about those who are using VPN can the same ODBC mentioned above work since this is just a network or an extension of the local network as long as the server instance name is correct
(2)      Instead of hard coding the server name, I want to create an extra table to be storing the server names so that if the user is using the application for first time then they need to key in the server name which must off course be stored in the table. For example, the table will look like below:
SQlID (PK)
ServerName (Txt)

Path: Driver={ODBC Driver 13 for SQL Server};server=localhost;database=WideWorldImporters;trusted_connection=Yes;


In VBA how do I reference the server name control where it says server=localhost
Currently I always ask the clients to give me the server name and manually type in the connection string in the development computer and compile the app before sending, for sure it does give an error after compiling due to change of the server name, but where it goes it never give any error because it will be pointing to the correct server, for example if the …
0
Experts,

Given the following Access Sql, how could I write an additional field that equals the percent of the total?
The code below produces two rows with attributes and dollar amounts. I'd like to add the Percentage of Total column at the end.
P&L Mapping         Entity     Custom1                Account    Amount              PctOfTotal
50GA1                      BU1       BU Subgroup1      COS          25,000,000            25%
56GA1                      BU1       BU Subgroup2      COS          75,000,000            75%

SELECT tblEntityMaster.[P&LMapping], [1tHFMSalesCos].Entity, [1tHFMSalesCos].Custom1, [1tHFMSalesCos].Account, [1tHFMSalesCos].Amount
FROM tblEntityMaster LEFT JOIN 1tHFMSalesCos ON (tblEntityMaster.HFMBaseEntity = [1tHFMSalesCos].Entity) AND (tblEntityMaster.Custom1 = [1tHFMSalesCos].Custom1)
GROUP BY tblEntityMaster.[P&LMapping], [1tHFMSalesCos].Entity, [1tHFMSalesCos].Custom1, [1tHFMSalesCos].Account, [1tHFMSalesCos].Amount
HAVING (((tblEntityMaster.[P&LMapping])<>"N/A") AND (([1tHFMSalesCos].Entity)="ES_08") AND (([1tHFMSalesCos].Account)="COS"));

Open in new window


In addition, there is another line in the table Where Custom1 = "TotC" that has the total of the two values above, its just filtered out.

Thanks
0
I have a search form that looks for the first instance of a record, such as last name and opens the corresponding form, employee.

But what if there are more than one person with the same last name, that is when I would like for the next and previous record button to become available.

I know there is a way of doing this,
Something along these lines, just not sure of the syntax.

If Record count > 1 then
me.previous.enabled = true
me.next.enabled = true

John

End If
0
I have a report that has groups.  I want to somehow number the groups on the report so the first group is "1 of 2", the second group is "2 of 2".  Kind of like page numbering but with groups,  How can this be done?
0
We have a Client record.  This has various fields as expected, including ClientID
We also have Invoice records.  These are associated with a Client via ClientID.
The Invoice records also have fields – Paid (yes/no) and Waived (yes/no)

On the Edit a Client form I want to have a button that will “Cancel” the Client.  This means make the client inactive, but I want to ensure that there are no unpaid invoices remaining.
So the code needs to do….

•      Search for an associated Invoice that is unpaid (and not waived)
•      If none exit..
•      Offer the user an option to set as Waived (another yes/no invoice field)
•      Set the invoice as waived
•      Search for a second qualifying invoice
•      If none exit..
•      Set Client to Inactive.

I have done a small amount of VB in the past but I cannot figure out how to go about doing the above and need a few clues please.

Regards
Richard
0
I am trying to use this as a control source on an report field but seem to have a syntax issue.  Can someone spot it?

= "Location: " & [Location1] & " " & "Design: " & If IsNull([StockDesign1], [DesignID1],[StockDesign1]) & " " & "Color: " & [DesignColor1]
0
I would like to add a message box or a pop up that is displayed while the record is updating and once complete it closes the message and the window.

This is what I have in the Form_BeforeUpdate event:

If Me.NewRecord Then
        Call AuditChanges("Employee_ID", "NEW")
Else
        Call AuditChanges("Employee_ID", "EDIT")
End If


I wouldlike a MsgBox to display that goes away once the changes are recorded.

Users are not liking that the screen tells them nothing of what is going on while they wait for it to close.

Thanks,
John
0
Finally my point of sales is complete but with one more help required here:

(1) When a product code is selected , automatically the selling price, quantity sold , taxes and the line total are completed , it also allows to alter the quantity sold just in case the customer has bought more than 1 quantity.

Assistance required

Since the whole line is completed automatically how can I make the cursor move to a new line (add new line) without manually again entering through the whole line?


Regards

Chris
0
Hi Experts,

In Access 2010 I want a form, when 'moved' to a specific place on the desktop (within Access) to open there the next time it is opened after its been closed.

Is there a simple way to do this?

Thanks,
Bob C.
0
We are now required to be scanning the bar codes for the supermarket stock using the point of sales ms access software when selling:

(1) all the stock are stored in the inventory table using the bar codes in place of stock codes
(2) Some stocks can be manufactured within the company some are ordered from outside if there are some pick periods especially monthends

Assistance required:

(1) To avoid wrong bar codes capturing in the software, is it possible the Ms Access 2016 built software can allow the bar codes scanning as a method of imputing the stock /Bar code in the system? There is a text control in a form for imputing bar code which require imputing the bar code manually

(2) Since  the cash sale form has a similar text control but in a combobox control form is it possible to be scanning the same bar codes here also, remember the bar codes are stored in the product table  hence the reason for the combobox in the cash sale form to easy lookup?

(3) Since the prices are linked to the product codes/ Bar codes such that once the product code/ bar code is selected the price also come automatically, now the question in the text control where we are required to capture the stock sold, is it possible to have the control show a default one (1) quantity sold automatically? or if the customer is buying more than one then the quantity can be over written manually?

Regard

Chris
0
I have 6 users with front end on pc's all linked to the same data on a server.

Between the 6 users, my app crashes 12-15 times per day. I have completely exhausted comparability issues, with great help from contributors on EE and I am now confident that this is not the issue.  Literally 9 out of 10 issues are disc or network error usually requiring a restart and very rarely a reboot. I would love to think that the issue is server related but here is the strangest part, 3 of the users are on windows 7 and have no issues at all, all problems are associated with the three users on Windows 10 with Office 365 of those, 2 users are 64 bit systems and one 32 bit.

We got over the issue of "The database is in an inconsistent state" and have applied the patch. This has not been a problem since and the installation now reports very very rarely does an issue require a reboot.
0
17 years ago I designed a Access database for a client.  5 years later they had the backend converted to SQL.  They have asked me to make some changes for them.  I have almost no SQL knowledge.

The FE is still Access and I have been making changes to it.  My question is if I need to add a field or create a new table in the BE is it difficult to do for a beginner?   Or is it similar to an Access BE?
0
I created an Access 13 accdb 32 bit and tried to open it with Access 16 64 bit and got this
C1
Previous discussions seemed to suggest that different versions worked with each other. I know I am mixing 64 bit and 32 bit with Access 13 and Access 16 but as I am not using any runtime versions of access and I am just trying to open with the full access 16 package that there should be no issue.
0
Hi
The following code produces a string "6.396"
I don't want / need the $.
But I would like strCost = "6.4"

Is it normal to round-up  to achieve this or is there a better way (function or something)?


           
Dim Cost As Currency:      Cost = 5.33 * 1.2
Dim strCost As String:     strCost = CStr(Cost)

Open in new window

0
Hi, I am looking for solution how to lookup a value and return cell address after posting in Excel from Access.
I found many examples of how to search string and return cell address online but mainly are for Excel VBA.
From Access form, my intention is to search Column A for a string at the active Excel Workbook for value = "Total" and return with Cell Address (sometimes with multiple "Total" in different Cells in Column A, therefore by using Loop is a must), then programmatically draw Top and Bottom Border line at the two cells next to any "Total" found , as shown in Example.xls.

The code to draw the top and bottom border are as follow,

'----------Draw Line----------
    With xlWSh.Range(xlWSh.Cells( Cell Address "Total" + 1, 2 ), xlWSh.Cells( "Cell Address Total" + 1, 3)).Borders(-4160) 'Top Border
        .LineStyle = xlContinuous
        .Weight = 2
    End With
    
    With xlWSh.Range(xlWSh.Cells( "Cell Address Total" + 1, 2), xlWSh.Cells( "Cell Address Total" + 1, 3)).Borders(-4107) 'Bottom Border
        .LineStyle = xlContinuous
        .Weight = 2
    End With
'----------End--------

Open in new window


thx in advance.
Example.xls
0
Dear Experts,
I upgraded Access from 2010 to 2016, and am trying to run the Runtime version on one of the client's computers.  The database file resides on shared drive, but there is no "File->Option" to add the network file to the trusted location in the 2016 Access Runtime version.
Please advise.
0
Can conditional formatting have more than one criteria?  e.g. If I want the background color to be red if the Officers name is Williams, or Jones or Smith.  Or the ID is 333, or 5454, or 66544?
0
Hi, I am using the following VBA to generate Excel Book1.xls and posting Access Table1 into sheet1.  After Table1 posting and while the Book1.xls is opened, I hv a hidden form running a For Next loop to generate data for Table2. I like to post second Access Table2 into the same Book1.xls Sheet1 after the For Next loop, how do I do that?

Private Sub Command1_Click()
    Dim CountRecord As Integer
    Dim rst As DAO.Recordset
    Dim ApXL As Object
    Dim xlWBk As Object
    Dim xlWSh As Object
    Dim dbs As DAO.Database
    Dim nLastRow As Long
    DoCmd.SetWarnings (WarningsOff)
    
    Set dbs = CurrentDb
    Const xlCenter As Long = -4108
    Const xlBottom As Long = -4107
   
    
    Set ApXL = CreateObject("Excel.Application")
    Set xlWBk = ApXL.Workbooks.Add
    ApXL.Visible = True
       
    Set xlWSh = xlWBk.Worksheets("Sheet1")
    If Len(strSheetName) > 0 Then
        xlWSh.Name = Left(strSheetName, 34)
    End If
    
    Set rst = dbs.OpenRecordset("Table1", dbOpenDynaset)
    xlWSh.Activate
    rst.MoveFirst
    xlWSh.Range("A1").Select ' Header Posting
    For Each fld In rst.Fields
    ApXL.ActiveCell = fld.Name
    ApXL.ActiveCell.Offset(0, 1).Select
    Next
    xlWSh.Range("A2").CopyFromRecordset rst ' Data Posting
    rst.Close
    Set rst = Nothing
    
    ' Hidden form running a For Next Loop data calculating..
    'Activate Book1 Sheet1 follow by Table2 posting........
    'e.g.
    .
    .
    .
    xlWSh.Activate
    rst.MoveFirst
    

Open in new window

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.