[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

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

How do you write a simple VBA coding for check boxes to filter data in a listbox.

On Form A I have several categories to check Category A, B, C that is base on yes/no. I want to make sure when I check Category A, just A shows on Form B. If left on unchecked then all categories show.
0
Exploring SharePoint 2016
LVL 12
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

I'm getting a Record too large error when I try to append one of my tables to a new table. I'm using MS Access 2K.  I suspect one of my users entered too much data into one of my fields.
I (1) need help finding the record causing the error... and (2) suggestions how to prevent it from happening again..

I know about the "2000" character limitation of Access records, but I can't find the offending record... it seems like someone must have written code???

I don't really understand Unicode Compression . it seems like it can affect this problem?  Can I mitigate the problem by changing a Unicode setting???  I don't need support for any language other than English.

I know I can delete unused fields, change to memo type, and normalize, but I right now I just need help finding the offending record.
Thanks in advance.
0
This isn't for work, just something I'm playing with in Access 2013

I am developing a routine that needs to read thru all files in a directory.

This is what the routine needs to do:

Delete any files in the directory with the string "_2", _3", "_4", "_5" or "_6" embedded in the file name as these are duplicates.

Rename each file in the directory by manipulating the name.

The contents of the directory are a list of songs that I've downloaded.  The files are named [Artist] - [Song Name].mp3

I want to rename them to [Song Name] - [Artist}.mp3

Here's some examples of the actual file names.  They all fit this pattern.

The title of the song and the contributing artist are apparently in the file information (the "Title' and "Contributing Artist" columns/metadata?) but I don't know how to access this information for each file in the table.

Directory Format


Any help would be appreciated.
1
Access 2003
I set Breakpoints, but when I run the Subroutine, the program does stop at the Breakpoints
Very simple question:  How does one enable breakpoints
0
Hi

In Access 2010 Professional a user using my Access solution gets the following error while trying to open a report with a query behind it.
Someone else in his office also running Access 2010 does not get any error and I don't get any error.

The expression in one of the queries that I believe is causing this is as follows

InvNum: Nz(DLookUp("[Invoice No]","Invoices","[ID] = " & Nz([Invoice ID],0)),"0")

Open in new window


Can I change my expression to something else that doesn't use Nz?

1
0
Hi,
I would like help to amend a formula to speed up calculations with an INDIRECT function.
Please refer to attached spreadsheet.
Many Thanks
Ian
Indirect-Function.xlsx
0
Hello,

My company's MS Access VBA software uses Azure Blob Storage and allows users to upload files from their computer to our blob storage. I have a user that requested for the file dialog box filter to default to what they last selected. Currently the filter always defaults to Image files and usually this user uploads PDFs. Is there anyway to get the filter to default to the last filter the user selected? Please see attached file to view the current code for the file dialog.

Thanks in advance!
File_Dialog_Code.PNG
0
I have two queries that return:

Gains Averaged
Losses Averaged  
And I want the final result to look like this:

Final Result
 

How can this be done?
0
Microsoft Access 2016
I want to define a parameter query to ask for a selection but return all records if left blank.
I am using Like [Enter Location] .... this works fine. but there are times I want to run the report for all locations.
I tried like [Enter Location] or Like * but this did not work.

Any suggestions would be greatly appreciated.
thanks
Wayne
0
Access:
Need my query to ask for input. I would like for this to pop open a box where I can input a variable for tbl_Tasks.New_Crafts.

TRANSFORM Count(tbl_Tasks.New_Crafts) AS CountOfNew_Crafts
SELECT tbl_Tasks.TASKSDescriptionCorrection, tbl_Tasks.TaskCombo, Count(tbl_Tasks.TaskCombo) AS CountOfTaskCombo
FROM tbl_Tasks
WHERE (((tbl_Tasks.New_Crafts)="e"))
GROUP BY tbl_Tasks.TASKSDescriptionCorrection, tbl_Tasks.TaskCombo
PIVOT tbl_Tasks.New_Crafts;
0
JavaScript Best Practices
LVL 12
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

I seem to have gotten what I wanted from earlier question, I have created below a table where I want to store two strings:
Tbllinkstring, see attached table

Now I want to use DLookup to be pick the string from the table, how do I use the IF and THEN and ELSE from the code below so that it evaluate and use the correct string, do not worry about the rest, I will take care of the rest, see the shaded area in the attached table:

Public Function SqlLinker()
Dim db As Database
Set db = CurrentDb
Dim tdef As TableDef
Dim constr As Variant
constr = "ODBC;DRIVER=SQL Server; " & _
"SERVER=USER\SQLExpress;DATABASE=Accounting;Trusted_Connection=Yes"
For Each tdef In db.TableDefs
If InStr(tdef.Connect, "ODBC") Then
tdef.Connect = constr
tdef.RefreshLink
End If
Next
MsgBox "Re link completed Successfully", vbOKOnly, "CA Premier Accounting Package"
End Function
For me this will be the best solution because before deploying the application I will be required only to change one of the links above in the table called Tbllinkstring to the correct host name, this means that I will still maintain my existing laptop links which allow me to compile and make an ACCDE copy without an error. Once the application is deployed to a new host name again it will simply drop my existing links after evaluating and use the new one. The advantage is that the users will not be required to select the correct link at all, the system will handle that for them.
On the shaded areas I want to …
0
Trying to convert Access syntax to SQL (Update Query)

UPDATE dbo.tbl_DriveSalesSidhilMatrix
Set tbl_DriveSalesSidhilMatrix.EnteredBy = case when OperatorName is null then Operator else OperatorName
From dbo.view_Z_SalesOrderAmmendmentsSums INNER JOIN
                         dbo.tbl_DriveSalesSidhilMatrix ON dbo.view_Z_SalesOrderAmmendmentsSums.Operator = dbo.tbl_DriveSalesSidhilMatrix.EnteredBy LEFT OUTER JOIN
                         dbo.tbl_Syspro_Operators ON dbo.view_Z_SalesOrderAmmendmentsSums.Operator = dbo.tbl_Syspro_Operators.OperatorCode
WHERE (dbo.tbl_DriveSalesSidhilMatrix.RecordSource = N'Sidhil')

I'm getting "Incorrect syntax near the keyword 'From'."

Can anyone help?
0
Hello Experts!

I have create a macro called AutoExec to run the function below:
Public Function SqlLinker()
Dim db As Database
Set db = CurrentDb
Dim tdef As TableDef
Dim constr As Variant
constr = "ODBC;DRIVER=SQL Server; " & _
"SERVER=USER\SQLExpress;DATABASE=Accounting;Trusted_Connection=Yes"
For Each tdef In db.TableDefs
If InStr(tdef.Connect, "ODBC") Then
tdef.Connect = constr
tdef.RefreshLink
End If
Next
MsgBox "Re link completed Successfully", vbOKOnly, "CA Premier Accounting Package"
End Function

Well the function links the tables in SQl Server at runtime and if the ODBC 17 path is correct a message with Re link completed Successfully pop up. All is well as long as I continue using my laptop the problem comes up if I install the same database on a different server client stand alone network not connect with my laptop because the new path is supposed to be:
constr = "ODBC;DRIVER=SQL Server; " & _
"SERVER=PETER\SQLExpress;DATABASE=Accounting;Trusted_Connection=Yes"

Now how best I can be changing the path below to be reference the correct server name? For example if the same name changes to CHRIS is there a way to alter the path below:

constr = "ODBC;DRIVER=SQL Server; " & _
"SERVER=USER\SQLExpress;DATABASE=Accounting;Trusted_Connection=Yes"

Example where there is USER change to CHRIS,the challenge here is how to change the code manually because we always send an accde format to clients. Some are suggesting to  put this string below in …
0
Experts,

I routinely build Access Databases as desktop processes to analyze data. I'm looking to understand the best way to handle the following situation:
Every query I run joins tables on Cost Center. My Cost Centers are 10 Char Alphanumeric. I've already learned from previous questions that I should create a Cost Center master table and use the Autonumber (or other short numeric) field as a CostCenterID for use in joins because its more efficient.
So now let’s say I have a
1.      Cost Center Table with 2000 cost centers and a CostCenterID (Autonumber) field that I’ll use as my primary key, and a
2.      Transaction Table with 1mil records that I want to join on the Cost Center table, but the CostCenterID does not yet exist in the Transaction Table.

How is performance effected if I:
1. Use an Update Query to add the CostCenterID from the Master Cost Center table as a foreign key to any Transaction Tables, so Cost Center and Transaction tables can be joined directly, or
Use Interim Query/Tables
2. Use a Select query to create an interim relationship.
SELECT tbl_Trans.CostCenter (alphanumeric), tbl_CostCenter.CostCenterID (Autonumber)
FROM tbl_Trans INNER JOIN tbl_CostCenter ON tbl_Trans.CostCenter = tbl_CostCenter.CostCenter

Open in new window

3.      Create an interim table with a make table query
SELECT tbl_Trans.CostCenter (alphanumeric), tbl_CostCenter.CostCenterID (Autonumber) INTO tbl_Bridge
FROM tbl_Trans INNER JOIN tbl_CostCenter ON tbl_Trans.CostCenter = tbl_CostCenter.CostCenter

Open in new window


One opinion I was given is that option 1 is best. I just want to make sure as I will be basing many processes going forward off of this method

Thanks!!
0
Hi Experts,

I have the following code which loops thru all fields from a text data file (csv), reads the value and constructs a string.
Would like to have the following modification.
For each field containing a date/time value, check if the time is midnight like "2018-08-01 00:00", in that case should change the time to either 24:00 or 00:00 AM

For Each ColumnName In columnsName

                    fieldName = Replace(ColumnName, " ", "")

                    fieldValue = Mid(columns(c), 2, Len(columns(c)) - 2)

                    patient(fieldName) = fieldValue

                    c = c + 1
Next

objHTTP.SetTimeouts 0, 0, -1, -1
objHTTP.Send JsonConverter.ConvertToJson(patient)

Open in new window

0
Is it possible to run some VB.NET code from within MSAccess!   MSaccess is my main frontend and I have
a VB.net module I'd need to run!  This is a service to access a webservice and I suppose I could write equivalent code in VBA
but was hoping to start adding VB.net code gradually to later convert everything to VB.net!  I've never use VB.net
but want to start learning it  piece by piece!

This is a snippet of code to give you an idea!  I ask because there are visual studio .NET libraries available so I assume I can!

 
Dim manualWebClient As New System.Net.WebClient
            manualWebClient.Headers.Add("Content-Type", "application/soap+xml;  charset=utf-8")
            Dim bytArguments As Byte() = System.Text.Encoding.ASCII.GetBytes(
                "<soap12:Envelope xmlns:xsi='http: //www.w3.org/2001/XMLSchema-instance' xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:soap12='http://www.w3.org/2003/05/soap-envelope'>

Just looking for a few good suggestions to point me in the right direction!

Pete
0
I have this SQL code that is the record source for a datasheet form:

SELECT tblEstimateProofs.Quantity, tblProofTypesLU.Description
FROM tblEstimateProofs LEFT JOIN tblProofTypesLU ON tblEstimateProofs.ProofType = tblProofTypesLU.ProofTypeID
WHERE (((tblEstimateProofs.EstimateID)=[Forms]![frmEstimates]![txtEstimateID]));

Open in new window


If I use the form on a report is ends up looking like this:

Datasheet Example
But on the report I want to display:

3 Baseball + 4 Football + 1 Soccer Ball + 2 Ping Pong Ball + 5 Some Other Kind of Ball

(Note the + marks between each item)

How can I do this?
0
I've been using Rick Fisher's Find & Replace for MS Access for many years. Another developer is trying to purchase the software, but is getting no response from  Rick Fisher. Does anyone know if he's still selling it? http://www.rickworld.com/index.html
0
Hi

I am trying to sum up columns and get the result using below formulae in access query. values need to sum up are in text format ("0.00")

Total: Nz(CDbl([Spent]),0)+Nz(CDbl([To Spend]),0)

I am getting #Error

Please have a look

Thank you
A
0
Determine the Perfect Price for Your IT Services
Determine the Perfect Price for Your IT Services

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

Hi
I have Oracle SQL Dev version v17.4 with Java Platform 1.8.

I would like to connect to an Ms Access table but i can't see the tab. How can I add it?
Oracle Missing Ms Access tab
In an older Oracle SQL Dev, (1.5), i do have that tab.
0
How to balance forwarded in access report closing balance of previous day to be an opening balance for the next day.
Here is my format
1st Day = Initial Balance  - Fill + Credit = Closing Balance
2nd Day = Closing Balance will be the Opening Balance for the next day.

Tnx
0
I am using Access 2003 (yes I am no jokes).... I have a Combobox to look up information... (date of birth). Users types in the date and bingo we find a match and goes to the correct record.

The issue I'm having is that when a user starts to type and then deletes the initial entry Access still wants to look up the data in the table.  Currently I have the combo box set with a row source of: SELECT tMain.DOB, tMain.LName & ", " & tMain.FName, tMain.TrayNo FROM tMain ORDER BY tMain.DOB DESC

I think I need to somehow move this code out of row course and fire it after an update.  Does this sound right and if so how I can use VBA to execute the search when there are at least 8 characters in the combo box to start the search?  I was thinking in the event "after update" to check the len and if over 8 set the row source in the combo box to SELECT tMain.DOB, tMain.LName & ", " & tMain.FName, tMain.TrayNo FROM tMain ORDER BY tMain.DOB DES??? Doesn't seem like the best approach.

Thank you.
0
Is there any way to pass query parameter through vba in ms Access. Any attached example will be highly appreciable.
0
Access 2010   vba

I'm updating a field in a subform with a calculation from 2 other fields.

Is there an easier way to write this  code ?



' =====================================
Dim g As String
Dim j As String

g = Me!RENEWAL_ESCALATED_PRICE_INCREASE_FRM
j = Me!ESTIMATED_REVENUE_IMPACT_FRM

' ============================================
'  < 4%
If g < 4 And j < 250 Then
Me!ACTION_NEEDED_FRM = "Update Quote"
End If
' < 4%
If g < 4 And j > 250 And j < 1000 Then
Me!ACTION_NEEDED_FRM = "RSVP/Director Approval"
End If
' < 4%
If g < 4 And j > 1000 Then
Me!ACTION_NEEDED_FRM = "FVP Approval"
End If


' =========================================
'  >4  and < 8%
If g > 4 And g < 8 And j < 250 Then
 Me!ACTION_NEEDED_FRM = "Update Quote"
End If

'  >4  and < 8%
If g > 4 And g < 8 And j > 250 And j < 1000 Then
Me!ACTION_NEEDED_FRM = "RSVP/Director Approval"
End If

'  >4  and < 8%
If g > 4 And g < 8 And j > 1000 Then
 Me!ACTION_NEEDED_FRM = "RSVP/Director Approval"
End If

' ===========================================
'  > 8%
If g > 8 And j < 250 Then
Me!ACTION_NEEDED_FRM = "Update Quote"
End If
' > 8%
If g > 8 And j > 250 And j < 1000 Then
Me!ACTION_NEEDED_FRM = "Update Quote"
End If
' > 8%
If g > 8 And j > 1000 Then
Me!ACTION_NEEDED_FRM = "Update Quote"
End If
0
I have a form with a sub-form.  If the user is on the sub-form and leaves the 2nd field on that sub-form empty, I would like the tab to take him to the next field on the main form.  Is this possible?
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.