[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

I have a .Net developer creating a DLL that we are going to Reference in an Access VBA application.  However, whilst we can see the DLL methods (once referenced), he is struggling with a couple of aspects such as passing variables through and how best the code the .DLL so that Intellisense works as expected.

Whenever I have worked with external references in the past, I usually access them like this (rough example):

Dim obj1 As New TSDialler.Dialer
obj1.SignalKey = "ABC123456"
obj1.TelephoneDestination = "0123456789"
obj1.InternalID = "ZYX987"
If obj1.MakeCall = 1 Then
    MsgBox "Call made"
Else
    MsgBox "Call not made.  Error: " & obj1.ReturnMessage
End If

Open in new window


However, he has never worked in VBA and is struggling with the various aspects.  His list of questions is below, but I was wondering if anyone had any links/suggestions for him to look at (more than just answering his questions).

Which kind of methods works?
Do properties work?
Constructors work, what about parameters?
How to do event pattern for a custom class and not UI control?
What pieces (that we use regularly) in C# that does not work with VBA when referenced?
Any article/doc that can be referred that has details for questions like above?


Any help would be greatly appreciated - sorry it's so vague.
0
Hi there we have this customer who is using someone else PC on the network to access all the files are shared from their PC. The PC that is sharing everything has no password and the permission is set to everyone.  The PC accessing all the shared files can access everything but access database files.
 They can access some of the database files but not couple of the database files because on the PC that is sharing them some of  the databases are linked to a database called "Address" so When ever they  open any of the database that is linked to the main "address" database it wont let them use it on the pc that is being shared to.

Is there away to change the directory patch on the PC the files are being shared so the PC that is getting all the files can view this databases and edit them.

Screenshot below of the error message

Screenshot
0
Hi

Having worked through the SQL Server as a backend to Ms Access FE I want to make a code that will check the connection strings that is valid from the access FE module, for example if the FIRST string below is wrong then it should check the SECOND string or return connection FAILED result:
(1)      sConnect = "ODBC;DRIVER={sql server};DATABASE=YourDatabaseName;SERVER=YourServerName;Trusted_Connection=Yes;"
(2)      strConnect = "ODBC;Driver={SQL Server};UID="& Me.txtUser & ";PWD=" & Me.txtPwd & ";"
From the above situation how do I use CASE SELECT  , END SELECT  in a VBA code? Do not worry about a full code I have a working code intact, its working fine.
I have put the same working VBA code in a module and is call by a macro called autoexe, now what to achieve with suggested selection is that before deployment to some clients I have to collect some as follows:
(1)      Computer Number ( for security VBA code purpose)
(2)      Computer name ( to used in the connection string above )
The current VBA will continue working silently without the user knowing what is happening, so in short string (1) will have my computer/server details while string (2) while have the new details for the new client computer name (Where the application will be deployed). The reasons for the above suggestion is as follows:
(1)      If I compile the application based on my computer connection it will fail to connect the new client computer because the server name will be different , hence the required  2 string …
0
access 2010
excel 2010

What I need: vba routine in Access
From Access i need to clear certain CELL range(s) in an Excel workbook and a specific sheet.
Workbook name "MyWorkbook.xlsm"
Worksheet "Import_Export_Data"

I need to clear certain CELLS on worksheet.
A4 TO A300
B4 TO B300
E4 TO E300
G4 TO G300

AND KEEP THE PRESENT formatting in the cells after clearing
Thanks
fordraiders
0
i have outlook email with huge content. i like to search in that email say xyz
if i click control+F it is opening new reply email window.

what is the shortcut to search email content
not sure why there is no search button
please advise
0
I'm using MS Access 2000

When my customers place an order they specify a manufacturer and a manufacturer part number.
Each line item (manufacturer and Manufacturer Part number therefore has a line item price (quantity * unit price)

I need help writing a query that will only show me the most expensive line item purchase from each customer.

For example, if a customer buys 100 pieces Motorola 2N2222 which cost .10 each the line item cost is $10
if that customer also buys 500 pieces of Texas Instruments SN7400N for .20 each the line item cost is $100
My report should generate a row that looks like:
01234 John Doe Texas Instruments $100
01235 Bill Doe    AMD $50

and so forth

Here is a query that lists all parts purchased.  I need to modify it to only display the largest purchase (one row for each customer)
Can anyone help???

SELECT ManufacturerMaster.ShortName, InvoiceHistory.PartNumber, Max([Price]*[quantity]) AS Amount
FROM InvoiceHistory INNER JOIN ManufacturerMaster ON InvoiceHistory.LineID = ManufacturerMaster.LineID
GROUP BY InvoiceHistory.CustomerNumber, ManufacturerMaster.ShortName, InvoiceHistory.PartNumber
HAVING (((Max([Price]*[quantity]))>0));
0
I have a subform with 7 columns bound to a table.  Two of the columns in the subform, named cmbStock and cmbBin, are combo boxes for selecting values.  The Row sources for each combo box are as follows:

cmbStock -  SELECT [Stock Locations].[Stockroom] FROM [Stock Locations] ORDER BY [Stockroom];
cmbBin -  SELECT [StockLocation_Bins_B].[Bin], [StockLocation_Bins_B].[InventoryLocationDescription] FROM StockLocation_Bins_B ORDER BY [Bin];

StockLocation_Bins_B is a select query.  

In the On Current Event for the subform I have the following code which dynamically changes query StockLocation_Bins_B based upon the value in cmbStock and then requerys cmbBin.  Although the query does get changed correctly the values in cmbBin do not change.  What am I doing wrong?

Dim QDFDynamic As QueryDef
    Dim SQL01 As String
    Dim SQL02 As String
    Dim SQL03 As String
    Dim SQL04 As String
   
   Set QDFDynamic = CurrentDb.QueryDefs("StockLocation_Bins_B")
   SQL01 = "SELECT dbo_FS_InventoryLocation.Bin, dbo_FS_InventoryLocation.InventoryLocationDescription "
    SQL02 = "FROM dbo_FS_InventoryLocation "
    SQL03 = "WHERE (((dbo_FS_InventoryLocation.Stockroom)='" & cmbStock & "')) "
    SQL04 = "ORDER BY dbo_FS_InventoryLocation.Bin;"
    QDFDynamic.SQL = SQL01 & SQL02 & SQL03 & SQL04
    QDFDynamic.Close
   
    cmbBin.Requery
0
I am trying to get a DLOOKUP to work in a select query in ms access. The fields "RawDiameter" & "StockRemoval" are numeric in tblClass1. The fields "RMDia" & "StkRemoval" in my query are text so I converted them to numeric in my dlookup. And then the field "MaterialClass" is a text value in tblClass1. And "GrindClass" is also a text value in my query. But when I try to run my query the column where the DLOOKUP is comes out all blank. Is the syntax incorrect for my dlookup?

SELECT dbo_vwJobList.JobNo, dbo_vwJobList.Quantity, dbo_vwJobList.GroupCode, dbo_vwJobList.FGDia, dbo_vwJobList.SetUpMins, dbo_vwJobList.ProdMins, dbo_vwJobList.Passes, dbo_vwJobList.RMDia, dbo_vwJobList.FGTol, dbo_vwJobList.GrindClass, dbo_vwJobList.StkRemoval, DLookUp("[GrindTimeS1]","tblClass1","[RawDiameter] >= 'Val([RMDia])' And 'Val([StkRemoval])' <= [StockRemoval] And [MaterialClass] = '[GrindClass]'") AS Pass
FROM dbo_vwJobList;

Open in new window

0
Hi Experts,

I have a linked table to a file in XLSX format, trying to change it to link to a CSV format instead, and having the following issue.

I have a query updating my Access table according to some values in that linked Excel table by joining two fields.

However since converting file to CSV format, I'm getting the error attached .1
This is the SQL.

UPDATE Skilled_Nursing_Visit_Note INNER JOIN V_Visit_Note_Export ON Skilled_Nursing_Visit_Note.SNV_ID = V_Visit_Note_Export.SNV_ID SET Skilled_Nursing_Visit_Note.Client_Last_Name = V_Visit_Note_Export.Client_Last_Name, Skilled_Nursing_Visit_Note.Status = V_Visit_Note_Export.Status
WHERE (((Skilled_Nursing_Visit_Note.Status)="draft") AND ((V_Visit_Note_Export.Status)="completed"));

Open in new window


Note - Skilled_Nursing_Visit_Note is an Access table, V_Visit_Note_Export is the CSV linked table.
0
A have an Access form and have connected it to an sql server table  using ADODB by setting  the  form's recordset property to the ADO recordset.  The first record comes up fine, but when I navigate to the next record, my ADO recordset property goes to a state of  0.  While the form displays the next record in the set, the form's OnCurrent event won't fire.
What is closing my recordset?  I'm not doing any requering or anything?
0
Need help with getting text box (txtJobNo) to state Job Ticket No. once a row is selected in either Subform A or Subform B.
I've tried the following but it doesn't display any information.

Is there another event I need to try or do I have the wrong syntax?
Please note provided sample.

Private Sub txtSyncSubforms_AfterUpdate()

  Dim lID As Long
  lID = Forms![fMainMenuTimeSheetMonthly]![sfTimeSheetMonthlyDescOnly].Form!("cboJobTicket").Column(0)
  txtJobNo = "Job Ticket No: " & lID
  
End Sub

Open in new window

EE-sample.accdb
0
I am working with a .Net developer to create a DLL that I reference from my MS Access application.  To help testing, I created a very basic Access 2016 db with a couple of forms and a module that will contain all of the code.  However, when I send him the .accdb file, the startup form does not load.  Plus, he can only go into Layout and Design view of the forms.  The odd thing is that he can open the module and run the test functions just fine (either from the Immediate window or directly).

He doesn't get any security warnings, it's pretty much the same version (slightly different revision), our security settings seem similar (we have compared Trust-center settings and added his location) - but nothing - the forms will not open normally.  Also, there are no compile errors.

I'm sure it's something to do with security  - I'm just not able to see the fix at the moment.

Any ideas would be very much appreciated.
0
I need help writing a ms access 2k query

I need a list of customers who have placed 5 or more orders and also spend $1000 or more since January 1, 2018
The problem is that if a customer orders three items at the same time I want to treat it as 1 order


The customer information is straight foward.

Prospects Table has the following fields:

CustomerNumber
CustomerName


The order information is in a table called InvoiceHistory
It has numeric price and quantity fields, so price*quantity is the amount of the order

InvoiceHistory Table has the following fields:
Price
Quantity
InvoiceNumber


The tricky part is that the invoicenumber which is a string field uses the first five characters for the invoice number.
Each item is appended to the invoicenumber.  

For example:

12345a
12345b
12345c

Should only count as one invoice, so left(invoicenumber,5) is acutally the invoice numbers that we want to count to determine the number of orders.

This is as far as I can get.  This following query counts all the invoices to calculate the count, so 12345a, b, and c count as three, and it should only count as 1.

Can anyone help???

SELECT Prospects.CustomerNumber, Prospects.CompanyName, Count(InvoiceHistory.InvoiceNumber) AS CountOfInvoiceNumber, Sum([price]*[quantity]) AS Expr1
FROM Prospects INNER JOIN InvoiceHistory ON Prospects.CustomerNumber = InvoiceHistory.CustomerNumber
WHERE (((InvoiceHistory.ShippedDate)>#1/1/2018#))
GROUP BY …
0
I have an access 2016 form which uses a linked SQL Server Table as its  Record Source, I have a Textbox control "booth_no" to add booth numbers. I can add the numbers but when I go to save it I get a Write Conflict error message and the only options I get is "Copy to Clipboard" and "Drop Changes", "Save Record" is grayed out. I selected selected the SQL table identity column which  is an INT as the Unique Record Identifier ([id] [int] IDENTITY(10001,1) NOT NULL,) when linking the table but it will not allow me to save the record. BTW all INT columns have a default value.
0
I am trying to export a query to Excel using the following code:

Sub ExcelExport()
Dim newFile As String
newFile = Application.CurrentProject.path & "\export.xlsx"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryExport", newFile, True

End Sub

Open in new window


The problem is, that the file seems to be corrupted.

Am I missing something?

Thanks

Massimo
Mutterschaftszeitfenster.accdb
0
Hi,

When launching an access database from the command line, one of my user experience a weird behavior.

The database is supposed to recieve a command line like the following (value returned by the Command() function):
import="291" database="C:\Users\userName\My documents\Application_DATA.accdb"

But he recieve:
import= 291 database= C:\Users\userName\My documents\Application_DATA.accdb
Quotes are stripped, and an additional space is inserted right after the "=" sign (or quotes are replaced by a space).

Do you see anyhing that can explain this behavior ?
0
(1) I'm required to deliver the access application system in a supermaket envirnoment, now the number of cashiers will be 20 (Users) accessing the same sales accounting or tables. My application is split into backend & front end of which per user will have their own front end linked to the backend on the server.My fear here is the locking system even if all my forms show the record locks property  - No Locks, any idea how go about this?

(2) I have also experienced some issues of power failure in some clients work place, now because of the same you find in the FE folder sometime their is an extra file generated after power failure any idea how to sort out this? Though my current advice to the clients is to invest in UPSs

Regards

Chris
0
I've been taking an Advanced Timesheet Sample online created by Helen Feddema and trying to advance it to allow for an entire months' worth of time entered.

My problem is where I need MS Access to freeze pane (similar in Excel) function the first three columns as you scroll to the right going further in the month.  So my idea was to create a main form with two subforms (A,B) that would cooperate as each row is touched and entered and would use the same temp table:  tempID to coordinate location.  

Question 1: (How to get key down access the other subform and then come back to original subform?)

Explanation:
I got the coding (key down) to go up within rows on a Continous (not datasheet) form - but not sure how I can get the cursor resting at that particular row in the second subform (B) go to that location AND then come back to its original position in subform A?  If I have to do it for each cell - just let me please have one example and I'll do the rest for the trouble.

Question 2:  (How to tab from one subform to another?)
Same thing needed when I tab from column 3 in subform A - has to go to column 1 same row in subform B...  I assume it will be the same coding except we will be looking for the TAB key press....

Question 3:
Finally as I scroll down horizontally and pick a new row - it will go also down to that row in subform B and then back to where you are...

Then it all has to work vice versa.  So I'll take what you can give me and make that work on …
0
checking syntax from sql server 2008 to  vba Access query to do the same thing

I have this sql server 2008 sql:
Update ro set [Realign_Using_Trackcode] in ('XXXXX')
 from [ss_program_workflow].[dbo].[t_nsc_trackcode_reassign_overrides] ro
 where [Realign_Using_Trackcode] in ('ZZZZZ')

then i have this access query statement in sql:
UPDATE dbo_t_nsc_trackcode_reassign_overrides SET dbo_t_nsc_trackcode_reassign_overrides.Realign_Using_Trackcode = "ZZZZZ"
WHERE (((dbo_t_nsc_trackcode_reassign_overrides.Realign_Using_Trackcode)="xxxxx"));


are they doing the same thing ?


Thanks
fordraiders
0
Unless you have time and patience don't bother with this question.
I am a novice with Access so I will ask lots of questions and need lots of direction.  I have eliminated some data to reduce the file size and I think everything still works. Also I know my table are the best set up so any advice in that direction would be greatly appreciated also.
I have an excel sheet which I have put into Access so I can hopeful build some reports on.
I am trying to separate the data into different tables.
The data concerns Equipment and the routine maintenance for each piece of equipment.
My biggest obstacle is building a report which shows a count for how many times a certain PM occurs.
The unique identifier for the equipment is 'AssetName'
The routine maintenance is set up as follows:
Each unique task has a 'TaskNum'
These are grouped by 'PM_NUM'  -  This grouping identifies what tasks are in a grouping.
Additional grouping was them done and the main grouping is 'TaskCombo'
On my report 'rpt_TasksGrouping_Counts' I need to get a count of how many times 'TaskCombo' occurs by Trade and then get a total duration.

The report is now showing how many lines of task there are in each 'TaskCombo' by Trade and the total duration by 'TaskCombo'

Example: Taskcombo SAPPM00010  occurs only once, it has 4 lines of tasking, each line requires 0.0001 for duration and it is for the 'O' (Operator Trade).

I would like it to show the following:

SAPPM00010 occurs 1 time for Trade 'O' …
0
I am totally new to learning vba and access. If someone could teach me that would be appreciated. I have a command button and text boxes. Basically, I want to enter a value in the text box, and populates the rest of the text boxes related to that value.
0
Hi

I want to use the currency function below which is in a module in my application, I intend to use it as follows:
For example total invoice value control ([txtinvoiceToal]), I want to use it in its control source like =English([txtinvoiceToal]) so that I get value in words. How can I add example Pesos, Rands Or kwacha at the beginning of the converted values?

Any idea?

Function English(ByVal N As Currency) As String

   Const Thousand = 1000@
   Const Million = Thousand * Thousand
   Const Billion = Thousand * Million
   Const Trillion = Thousand * Billion

   If (N = 0@) Then English = "zero": Exit Function

   Dim Buf As String: If (N < 0@) Then Buf = "negative " Else Buf = ""
   Dim Frac As Currency: Frac = Abs(N - Fix(N))
   If (N < 0@ Or Frac <> 0@) Then N = Abs(Fix(N))
   Dim AtLeastOne As Integer: AtLeastOne = N >= 1

   If (N >= Trillion) Then
      Debug.Print N
      Buf = Buf & EnglishDigitGroup(Int(N / Trillion)) & " trillion"
      N = N - Int(N / Trillion) * Trillion
      If (N >= 1@) Then Buf = Buf & " "
   End If

   If (N >= Billion) Then
      Debug.Print N
      Buf = Buf & EnglishDigitGroup(Int(N / Billion)) & " billion"
      N = N - Int(N / Billion) * Billion
      If (N >= 1@) Then Buf = Buf & " "
   End If

   If (N >= Million) Then
      Debug.Print N
      Buf = Buf & EnglishDigitGroup(N \ Million) & " million"
      N = N Mod Million
      If (N >= 1@) Then Buf = Buf & " "
   End If

   If (N >= …
0
I am developing an employee database which displays the employee's Picture when his/her record is selected.
The Picture is displayed using the following procedure:

Private Sub Form_Current()
  On Error GoTo ErrorPicture
    imgPicture.Picture = CurrentProject.Path & "\fotos\" & emp_Picture
    On Error GoTo 0
    Exit Sub
    
ErrorPicture:
    On Error Resume Next
    imgPicture.Picture = CurrentProject.Path & "\fotos\_Person.png"
End Sub

Open in new window


The Problem is that the image is added to the hidden MsSysResources table.
With almost 300 Pictures, the database has grown from 5 MB to 200 MB in size.
Is there a way to not store the images in this table?

Thanks for your feedback

Massimo
0
I have a little block of code that generates a date value based on three parameters. I need to use it in two places.
1.      SET a temporary variable in a stored procedure to the output of the block of code
2.      Assign it to a variable in a VBA function behind a MS Access form
I want to have only one version of the code. Either a SQL Server scalar function or a SQL Server stored procedure. I don’t care which.

Here is where I stand in trying to achieve the goal.
SET temporary variable in a stored procedure
Declare
    @RecurFreq int = 12      -- Periods per year
  , @RecurYr int = 2020      -- Year
  , @RecurSeq int = 2            -- Period
Declare @NewRecurDate date
-- Using this function works OK
      SET @NewRecurDate = dbo.fnGetPeriodEndDate (@RecurFreq, @RecurYr, @RecurSeq)
      SELECT @NewRecurDate as FromFunction

--      Executing this stored procedure works OK
EXEC dbo.rwGetPeriodEndDate @RecurFreq, @RecurYr, @RecurSeq
--      But I have not found a way to SET the @NewRecurDate variable to the result of the stored procedure execution

If that’s not possible, then I can go the other way. How do I call SQL Server from my VBA code to execute my fnGetPeriodEndDate function?
Here’s the VBA code:
Dim NewPeriodEndDate As Date
‘This works fine VBA knows how to deal with assigning the result of a stored procedure to a variable.
        NewPeriodEndDate = ExecuteScalar("dbo.rwGetPeriodEndDate @RecurFreq = " & Me.grpRecurFreqSel & ", @RecurYr = " & Me.RecurYearSel & ", @RecurSeq = " & RecurSeq)
0
Hi Experts,
Is there a way to use a single keyboard key to perform the following?
Shift-F8      Step over the line by running the procedure on the current line without going into it and stepping through each line
Thanks
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.