Microsoft Access

221K

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.

Share tech news, updates, or what's on your mind.

Sign up to Post

This code works fine in Excel, I don't know what an alternative to this would be in Ms access. Any help is greatly appreciated.

Dim ctrl as control
Dim ctrlName as string
ctrlName = "whatever"
Set ctrl = Me.Controls.Add("Forms.Label.1", ctrlName, True)
0
Build an E-Commerce Site with Angular 5
LVL 13
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Is there a way to output 2 additional headers when exporting a query in Access to an Excel file?

The picture shows 3 header lines. Example file
0
How can I capture an image (jpg, png, gif - some common image type) of the entire content (not just what in currently in the view, but the entire scrollable webpage) of a webbrowser control in an Access database form?
0
I am using the code below on a userform in Excel to run a report stored in access database. It currently prints the report immediately. I am trying to have the report just display without printing. I would prefer that the report opens in excel and the user can then decide to print or discard.

Right now if the user runs the report it locks the access dba until the print job is complete. I need to prevent that from happening since we have multiple writing to the database from the excel userform. Any suggestions or perhaps a better method?


Private Sub CommandButton2_Click()
       Dim objAcc As Object
       Dim strWhere As String
       strWhere = "IDnNo = " & Me.txtBrNo & ""
       Set objAcc = CreateObject("Access.Application")
       objAcc.OpenCurrentDatabase FilePath:=TARGET_DB
       objAcc.DoCmd.OpenReport "rptClasss", acViewPreview, strWhere
       On Error GoTo ErrHandler
       objAcc.RunCommand 340 
       On Error GoTo 0
       objAcc.Quit
       Exit Sub
ErrHandler:
       If Err = 2501 Then 
           ' Ignore
       Else
           MsgBox Err.Description, vbExclamation
       End If
       Resume Next

End Sub

Open in new window

0
I am trying to write a function that runs Visual Cut, but I get compile errors. Please help me resolve the errors.

Error Detail:
(1) Line of Code:
Set RetVal = Shell(VisCutApp & "-e " & " " & ""Export_Format:Adobe Acrobat (pdf)"" & " " & ""CR_Path""", 1)
ERROR: "Expected: list separator or )" error caused by "Export_Format"

(2) Line of Code:
"C:\Program Files (x86)\Visual CUT 11\Visual CUT.exe"
ERROR: "Type mismatch" error

Code with errors:

'Function RunVisCut(CR_Path As String)
Dim VisCutApp As Object
Dim RetVal As String
Set VisCutApp = "C:\Program Files (x86)\Visual CUT 11\Visual CUT.exe"
Set RetVal = Shell(VisCutApp & " " & "-e " & " " & ""Export_Format:Adobe Acrobat (pdf)"" & " " & ""CR_Path""", 1)
End Function

This code runs without any errors:
Dim ls_temp As String
ls_temp = "C:\Program Files (x86)\Visual CUT 11\Visual CUT.exe " & _
"-e ""c:\temp\Sales.rpt"" ""Export_Format:Adobe Acrobat (pdf)"" ""Export_File:c:\temp\Sales.pdf"""
Shell (ls_temp)
0
Most of the time either one of theses seems to work.  however, at some point something happens in the form where after execution of either of these statements, the column is still set to "1410"

Me.subform_RBWS_TA_TEAM_DATASHEET.Form(vSplitItem(0)).ColumnWidth = 0

Me.subform_RBWS_TA_TEAM_DATASHEET.Form(vSplitItem(0)).Properties("ColumnWidth") = 0
0
We have a screen in Access 365 which has a datasheet subform. We would like to customize the colour of the datasheet column headings from the grey, which we don't seem to be able to change, to pure white. Our Access 365 application is running on Windows Server 2012 R2 and users access the screens via Remote Desktop Connection or RemoteApp. There seems to be a default colour scheme coming in from Windows Server.

Sam Elliott
Job Tracker Technical Support
0
I am fumbling through trying to learn Access better.  I am trying to figure out how to use the charts on the forms but can't get it to start where I want.  

I took a table that has multiple years of sales data. I made a query where I summed the sales for all parent groups while grouping by date but I summed the data over specific date ranges that are dynamic. I created several fields like the example below.  I wanted to be able to compare sales from the last year to sales from the same point 1 yr ago and 2 yrs ago.

Last12M: Sum(IIf([orderDate] Between DateAdd("yyyy",-1,Date()) And Date(),[ZqryAmazonOrderHistory_ShipStationOrders].[quantity],0))
Last12M_1YrAgo: Sum(IIf([orderDate] Between DateAdd("yyyy",-2,Date()) And DateAdd("yyyy",-1,Date()),[ZqryAmazonOrderHistory_ShipStationOrders].[quantity],0))
Last12M_2YrAgo: Sum(IIf([orderDate] Between DateAdd("yyyy",-3,Date()) And DateAdd("yyyy",-2,Date()),[ZqryAmazonOrderHistory_ShipStationOrders].[quantity],0))

The query looks great.  I changed the date to be Date: Format([orderDate],"mmdd") so that I could chart the lines for 3 different years on top of one another.  

I am trying to create a form to display the graph of the sales among other things but I cannot get the chart in Access to start the data from the current month.  It always shows it as starting at 0101 and ending at 1231.  I have applied a sort order with the actual date to the query but the chart ignores that and even when I change it in the transpose …
0
I have an Access 2016 database with a table and query that I want to export to fixed width text files monthly (I give a data example below). The problem is that I can't guarantee that the access database structure will remain the same. Due to this I can't use the built in saved exports feature in access. I have copied and modified a script that will create a schema.ini for me on the fly with the proper schema formatting, including field names, field widths, etc. The problem is I am uncertain how to use it to export the data. I was reading this rather informative web page about how to work with a schema.ini file but it references how to use it for import, not export:


I was originally trying to use the TransferText method but I just keep running into errors with it. Most sites I am reading that are newer seem to recommend using ADO commands now.
DoCmd.TransferText acExportFixed, "schema.ini", tableName, "C:\TestFolder\TestFile.txt", True

I am willing to use any method that works and am open to suggestions.

An example of my issue is that in one month it would be that the database table has 2 fields, both width 10:
fieldID Integer
fieldUserName Char Width 10

Open in new window


But the next month it could have 3 fields and different sizes:
fieldID Integer
fieldUserFName Char Width 25
fieldUserLName Char Width 25

Open in new window


I have a script that can create a schema file on the fly to account for the structure changes but I am not sure how to export the data using the updated schema file.

Open in new window

0
I'm trying to figure out how to run a query that counts the unique (or distinct) values.  I'm having issues with the fields being similar.  Example, I have one contract with 2 different dates.  I only want to count the exact dates as one.  I've attached an example.  The query should only be showing a count of 3 records though, not 4.  Any suggestions are welcome.
ContractQuery.accdb
0
Learn SQL Server Core 2016
LVL 13
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

I have large table in access and I want to split it into smaller tables based on unique value in field one. Does anyone know of a query?

Thank you.
0
Hi
I have an Access database that has linked tables from another database.
I want to use VBA code to convert some of these linked tables into local tables
What VBA code would I use to do this?
Thanks
0
I am not sure if I have the correct understanding of KeyPress event in Access form. Can someone provide an example or explain it better than what I could find in MS Help?
0
We have an Access database that runs ok on a Windows Server, but we have significant performance issues when attempting to use the database when working from home over our VPN via Cisco AnyConnect. Since we all have laptops, and bring them home when working from home, there is no PC in the office to use RDP.

How do people normally get good performance when running MS Access remotely if they aren't using RDP?
0
Hi Experts,
Why are they few SQL drivers/databases listed under linked table manager when I only have one?
See attached.
Thanks
Untitled.png
0
When I sign in to an RDP session, start Access and get into the IDE to inspect module code the mouse does not scroll the window.  There is a scrollbar that I can drag with the mouse, but the mouse's scroll wheel has no effect.  The scroll wheel works in all other applications in the RDP session.

Is there a reference I need to link in?  Or is this configured in the RDP connection properties?
0
Dear Experts!

Thank you so much for the previous and current help on this Ms Access VBA Topic , I still need your assistance on how to get or pull all the data from the sub form called [sfrmInvoicedetails Subform] for my string Json string. The little salvage over the internet it appears I'm required to use EOF & BOF, frankly I'm not sure how to use these functions in VBA.

Final working code and approved see below:


Private Sub CmdSales_Click()
  
Dim foo As New Dictionary
Set foo = New Dictionary
Dim Noor As Dictionary
Set Noor = New Dictionary
Dim hoo As New Collection
Dim goo As New Dictionary
Set goo = New Dictionary
Dim Zoo As New Dictionary
Set Zoo = New Dictionary
Dim Koo As New Collection
Dim Too As New Collection
    With foo
        .Add "PosSerialNumber", Me.INV
        .Add "IssueTime", Me.InvoiceDate
        .Add "Customer", Me.Customer.Column(1)
        .Add "TransactionTyp", 0
        .Add "PaymentMode", 0
        .Add "SaleType", 0
        .Add "Items", Koo
     Koo.Add Noor
     Noor.Add "ItemID", 1
     Noor.Add "Description", Forms!frmInvoice![sfrmInvoicedetails Subform]!Description.Column(1)
     Noor.Add "BarCode", "4589630036"
     Noor.Add "Quantity", Forms!frmInvoice![sfrmInvoicedetails Subform]!Qty
     Noor.Add "UnitPrice", Forms!frmInvoice![sfrmInvoicedetails Subform]!UnitPrice
     Noor.Add "Discount", Forms!frmInvoice![sfrmInvoicedetails Subform]!Discount
     Noor.Add "Taxable", hoo
     hoo.Add 

Open in new window

0
i need to get values from a subform.

Before that happens, i just want to  make sure the subform is currently selected ?

if not then i need to flag them to select a row in the subform ?

Me!SubformName.Form.CurrentRecord  ?



Thanks
fordraiders
0
Hi Experts,
I have two questions:
1).  I have a database I named it for example Employee DB but when I tried to compile it, I saw the name under Debug it's "compile EmployeeNew", how could be happen like that and how do I change it that matches the name of the DB suppose to be?

2). I tried to add a "word Merge" in the Customize Quick Access Toolbar but when I open in the RunTime version 2016 (office 365 access Runtime 2016, I using office 365 access 2016 full version), it does not show the "word merge" for me to merge a report to word document. Is any other way I can add the "word merge" in the toolbar for users to use?

Thanks,
0
Ensure you’re charging the right price for your IT
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Been a while since I've done this.

Have just migrated a database from an old SQL Server on a 10 year old server to a newer (SQL Server 2008R2) server, on a newer server.  Clients IT guys really want to get them off that really old box.

Step 3 will be to migrate their A2003 database to O365, but for now, I need to reconnect the database from the old server to the new one.

I generally just do this, with a DSN-less connection, by changing the server name in the connection string, since they are using Windows Authentication and the new Server and old server are on the same domain.  But for some reason, I'm getting the following error message.2019-10-04_SQL-Server-Link-error.jpg
Anybody have any ideas where to start with this?
0
I use the "#" sign (i.e. JobPack#). In all cases I wrap it as [JobPack#] because the "#" is a reserved character. I am using that symbol to save on space in various places, etc.  THe vast majority of time it works fine.

However, in a few subforms, where I put an expression named as [JobPack#] and allow the user to click on column heading for sorting,  filter, etc.

It gives "Syntax error in date in query expression 'JobPack#'"  it does not do that in all subforms.

baring having to replace the "#" in this one case, is there a workaround?
0
We have an Access 2010 ADE file that we open using Access 2010 Runtime. On a new Windows 10 VDI template we have installed O365 Pro Plus and then installed Access 2010 Runtime SP2 with the remaining updates. The first time a user logs into the desktop the Access file opens without issue but once the user logs out the next time they login they get the error "the file is not in the correct format for a microsoft access project". If we open Outlook, Word or Excel (Latest Pro Plus version) before the Access program it opens fine but if you try the access program first you get the error above.

Any ideas what might be causing this and what opening Outlook, Word or Excel does to the Access 2010 file that allows it to open?

Thanks
0
SQL Primary table is Source. The key to the table is ID, data type INT.
SQL child table is Detail.  The key to the table is ID, data type INT.
A foreign key relationship defined.
  Foreign key base table: DETAIL
  Foreign key column:  TRACT
  Primary/Unique Key Base: SOURCE
  Primary/Unique Key Column: ID
The error happens when I create a single form for Source and then try to embed a subform for Detail. I get the error "Expression is typed incorrectly, or it too complex to be evaluated" I read online that I need to reference the table after the form was loaded. I removed the recordsource from the form. It would then open. After form was loaded I allowed the user to select a source. I then set the source for the subform with the following: Me!frmAnalysis1Marked.Form.RecordSource = "Detail"
I got the same error. So, I begin to suspect it is something with SQL.
Does anyone have a clue?
0
I'm using an Access pass-through query to attempt to retrieve the names of the tables and views in an Azure database with the following SQL:

SELECT Table_Schema
, Table_Schema + '.' + Table_Name as Table_Name
, CASE WHEN TABLE_TYPE  = 'Base Table' THEN 'Table' ELSE TABLE_TYPE END as Source
FROM INFORMATION_SCHEMA.Tables
WHERE Table_Schema NOT LIKE 'sys%'
ORDER BY CASE WHEN TABLE_TYPE= 'Base Table' THEN 'Table' ELSE TABLE_TYPE END, Table_Name

this seems to work fine when I have full permissions on the server (SQL Server), but does not appear to be retrieving the records I would expect to see when using an Azure database where I don't have full permissions on the server.  Does that make sense?  The connection string used for the pass-through query is the same as I'm using to link to the various tables from the specific database identified in the connection string.

Is there something I'm missing?  Is there a better query syntax to ensure I get all of the tables and views associated with the server and database in question?

Thanks,

Dale
0
I and using a drag and drop feature found on the web.The problem is that it opens to the last folder accessed.I am wondering if i can change the location last accessed in vba before opening the drag and drop.
0

Microsoft Access

221K

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.