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

For those who are familiar with Elookup  in Ms Access, see how you can help on the code below:

"Description", Elookup("ProductName", "QryJson", "InvoiceID =" & Me.InvoiceID & " AND ItemesID =" & CStr(i))

Open in new window


The above  code gives an error Elookup 3061 but the same code with Dlookup works perfect, I just want to see whether its true that Elookup is twice faster than Dlookup.

I have put the code below in a module:

Option Compare Database
Option Explicit

Public Function Elookup(Expr As String, Domain As String, Optional Criteria As Variant, _
    Optional OrderClause As Variant) As Variant
On Error GoTo Err_ELookup
    'Purpose:   Faster and more flexible replacement for DLookup()
    'Arguments: Same as DLookup, with additional Order By option.
    'Return:    Value of the Expr if found, else Null.
    '           Delimited list for multi-value field.
    'Author:    Allen Browne. allen@allenbrowne.com
    'Updated:   December 2006, to handle multi-value fields (Access 2007 and later.)
    'Examples:
    '           1. To find the last value, include DESC in the OrderClause, e.g.:
    '               ELookup("[Surname] & [FirstName]", "tblClient", , "ClientID DESC")
    '           2. To find the lowest non-null value of a field, use the Criteria, e.g.:
    '               ELookup("ClientID", "tblClient", "Surname Is Not Null" , "Surname")
    'Note:      Requires a reference to the DAO library.
    Dim db As DAO.Database          'This database.
   

Open in new window

0
When converting a split FE/BE MS Access o365 MDB program from 32 to 64 bits, does anything need to be done to the backend which only includes tables.. no code.

Also, is there any "conversion" process, or do I just update the code in the FE to work with a 64-bit process and then uninstall 32-bit office and install 64-bit office, then release my new FE?
0
I am testing two option buttons which call a procedure to open a PDF. Both option buttons only open file 1.pdf. OptFile2 will not open 2.pdf. Please help me resolve the error. The attached file contains the code.

RunAcrobat procedure:
Private Sub RunAcrobat(myPath As String)
Dim acropgm As String
Dim RetVal As Long
acropgm = "C:\Program Files (x86)\Adobe\Acrobat 10.0\Acrobat\Acrobat.exe"
RetVal = Shell(acropgm & " " & myPath, 1)
End Sub

Open in new window


Option buttons code:
If OptFile1.Enabled Then
RunAcrobat "C:\Temp\1.pdf"

ElseIf OptFile2.Enabled Then
RunAcrobat "C:\Temp\2.pdf"
End If
  ' Close form
   DoCmd.Close acForm, Me.Name

Open in new window


Works:
RunAcrobat "C:\Temp\1.pdf"
RunAcrobat "C:\Temp\2.pdf"
EE_100619.mdb
0
I have a form with many buttons on it. One button has  a simple query:
Select everything from TblMain where the RecDate =10/01/2019
if there are records they should open the results if there are no errors should be message box: there are no records
I need to create a macro
0
I'm getting an Access error "At most one record can be returned by this subquery.

DELETE tblEcapeInitatives.*
FROM tblEcapeInitatives
WHERE (tblEcapeInitatives.[ALJ Appeal Num])=  (SELECT  [ALJ Appeal Num] FROM [tblMigrated]);

Open in new window

0
I am trying to write a procedure that runs batch files, but I get an "invalid qualifier" compile error. Please help me resolve the error. The attached file contains the code.

The error occurs on "BatchFile."

Code:
Private Sub RunBatchFiles(BatchPath As String, BatchFile As String)

Dim varRetVal As Variant
'Dim varRetVal As Long
varRetVal = Shell(BatchPath & "" & BatchFile.cmd)
End Sub

Open in new window

EE_100619.mdb
0
HI all, i have sub sub continous form with combo box called payitems.  now i want to add new combo box called group where now payitems row source will depends on value selected in group com box.

Group combo box will have text values like A, B, C. When user selected A then only payitems which comes under that group will be displayed in the list for the user to select.

Now my problem is in payitems combo box row source i added condition as Select * from ------where Paitemgroup.group = [Forms]![frm_WorkOrder_PayItem]![Group] but for some reason though i selected value in group combo box it's still prompting  to enter that group value to display payitems.

Also please note that group value is "text" and not integer.
I tried using after update event of group combo box but still same problem persists. can someone help me where i am doing wrong.

Also as this is continous form i want to select different group in each row. because if i select group A in first row it's selecting as same group in remaining rows as well which i don't want that to happen.

can someone kindly help on this please? thanks in advance
0
I am getting this odd error - Experts-Exchange-Question-2019-10-2.docxseems to be substituting the "#" sign for the "." in the export file name


SpreadSheetName = Forms![0_masterdatafrm]![DefaultContrId] & " " & Forms![0_masterdatafrm]![DefaultContrIdTxt] & " Wkly Cnstr Mtg Notes " & Format(Me![MeetingNumber], "000") & " " & Format(Me![MeetingDate], "mm-dd-yyyy") & " " & TableToExport & ".csv"

MsgBox SpreadSheetName & vbNewLine & vbNewLine & "J:\Projects\_CM-OwnerRep\ProjMngmtDataBase\Ref Proj Spreadsheets - Current\" & SpreadSheetName
Results –
 
DoCmd.TransferText acExportDelim, , TableToExport, "J:\Projects\_CM-OwnerRep\ProjMngmtDataBase\Ref Proj Spreadsheets - Current\" & SpreadSheetName, False
Results –
0
Hi Experts,,
I have a MS Office 2016 Access application that includes code to import data from an Excel Spreadsheet.

For Users that don't have MS Access they can download and install MS Access 2016 Runtime and the application itself works perfectly.

However when they try to use the Import From Excel function they get an Error if they don't have Excel installed.

How can I / they get a Runtime Version of Excel installed so that they can use this function?

Thanks,
Bob C.
0
I have a ResGroup and a  ResDetails  table .  On the backend (server) i have defined the following relationship:

ResGroup (Gresid as key) 1 - Many with ResDetails(Gresid)

Now if I delete a record in ResGroup it will correctly delete also any attached ResDetails records. However, if I manually change or delete the GRESID key in ResDetails, it will still allow me to save ResDetails even if it breaks the referential integrity (each ResDetail must relate to one specific ResGroup). I don't understand why and what do I need to do to ensure this referential integrity? Thank you for any help. Cheers Michael
0
Hi
I am trying to use a DLookup as the Datasource of a text box. I need to reference
another text box called txtFK but am not doing it right

=DLookUp(Max("[EntryBy]"),"tblTransactionsCivil","[FirearmFK] =" & '[txtFK]')
0
Help me to replace the Dlookup below in Ms access VBA Code with a select query , I understand the performance will be very good:

DLookup("Quantity", "QryJson", "InvoiceID =" & Me.InvoiceID & " AND ItemesID =" & CStr(i))

Open in new window



Regards

Chris
0
Hi,
I must export a batch of .PDF files to Excel by matching as close as possible the original .PDF files (not just text transfert).
I have a subscription with Adobe Acrobat Pro but I can't find any examples of source code to help me with this challenge.
I'm already an expert with VBA through MS Access, but I'm very beginner at using Adobe Acrobat functions, objects and so on.
I really need examples written in VBA that can do that.
Thanks!
0
Having trouble in editing an existing macro within the Assets Tracking template in Access. I need the search macro to be able to search through a table and bring it back to the form. However, I have never worked with macros before and I am unsure how to do this. Attaching a picture of the area of the macro that needs attention, although to be honest I am unsure what needs to be updated. I've been replacing the old fields with the new ones. Snip of the macro
0
Hi all,

Hopefully this won't be too difficult but I'm not sure on how to proceed.  I have a sales order table and want to create an invoice import file from it.  I have no issues if each account only has one line item associated in the sales order table.  I am running into issues when there are multiple lines for each account.  Here is some data from the sales table (The first number is just auto assigned and the second number is the account number):

215931      2635                        109982J      34346      $12.00      12                              000001
215932      2635                        109982J      47017      $12.00      12                              000002
215933      950004                        109983J      34346      $12.00      12                              000001
215934      950004                        109983J      47017      $12.00      12                              000002
215935      950022                        109984J      34346      $12.00      12                              000001
215936      950022                        109984J      47017      $12.00      12                              000002

What I would like is to append all of the records to the invoice table and assign one invoice number to the first two lines and then move to the next invoice number and assign it to lines 3 and 4 and so on.  There won't always be two records either.  There may be over 10 records for each sales order.

Here is the code that I have that assigns a different invoice number to each line:
    For x = 1 To rstabledata.RecordCount
        rsInvoiceOrders.AddNew
            rsInvoiceOrders!ETInvNum = rsInvoiceNum!InvNum
            rsInvoiceOrders!InvDate = strDate
            rsInvoiceOrders!ETAcctNum = rstabledata!ETAcctNum
            rsInvoiceOrders!ItemCode = rstabledata!ItemCode
            

Open in new window

0
Command button

I have below code on a command button. When I run the command, I get a message Compile error: Method or data member not found. I am trying to close a report named RandSt_byState2_rpt     What do I need to change for the command to work?

Private Sub Command36_Click()
DoCmd.CloseReport "RandSt_byState2_rpt"
End Sub
0
I am trying to compact and repair a linked .accdb using this code:

strDBLocation = "C:\Install\BackendData\BE2_Data.accdb"
DBEngine.CompactDatabase strDBLocation, strDBLocation & "_1"
Kill strDBLocation
Name strDBLocation & "_1" As strDBLocation

I am getting an error message telling me that the file is open - on the very computer I am running it from. The app I am running the code from is linked the BE2_Data.

Does this mean I can't compact and repair a linked database from code if I'm linked to the .accdb I am trying to compact?

Thanks.
1
Hi Experts,

This is in regard of the following

https://www.experts-exchange.com/questions/29158415/Correction-of-date-field.html

Would like to expand the functionality suggested there to handle the attached list as much as possible.

So for example

01//01/19 gets converted to 01/01/19
01/011955 gets converted to 01/01/1955
01\01\1955 to 01/01/1955
01–01-1955 to 01/01/1955

and so on...

Thanks
InvalidDates.xlsx
0
Long names on text records cause issues when adding those records to our purchase orders. It seems like 65 is the string limit. I doing some quick testing and what im seeing is we are using If Me.cboLocalSKU.ListIndex = -1 Then to check that for a selected product, but when I swap that to If Nz(Me.cboLocalSKU.Column(1), "") = "" it will allow the products to be added, with all the data…

Thoughts anyone? Why would the app programmers have used ListIndex there?
0
Hi all,

I am fairly new to Access forms and coding.  I am not sure what I am asking is even possible.

I want a combo box on my form that lists the names of my tables that I have created.  For example:  The combo box will list all tables that start with Work_.  After I make my selection (Work_Employees), it will show the data of the Work_Employees table on the form.

Thank you for your help!
0
How do I add a checkbox to a continuous form so user can check the box and delete just that record OR check multiple records and delete the ones he checked?
0
Hello All;

After working very well in Ms Access 2016 the code below did not give any error, but the same interface when used against SQL Server 2016
it give an error:

Error no.3622

You must use the db see changes option with openrecordset when accessing a sql server table that has an identity column.

I double checked all the 120 tables in the sql server to see if there is one without a primary key I found non. This is now making me un popular on this site for sure, its very frustrating, imagine even after putting more man hours on this, there is still an issue, where did I go wrong now please advise.


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
    Set root = New Dictionary

    Dim transaction As Dictionary
    Dim transactions As Collection
    Dim item As Dictionary
    Dim items As Collection
    Dim invoice As Dictionary
    Dim invoices As Collection
    Dim Tax As Collection
    Dim i As Long
    Dim j As Long
    Dim t As Long
    Set transactions = New Collection
  Set db = CurrentDb
  Set qdf = db.QueryDefs("QryJson")
For Each prm In qdf.Parameters
    prm = Eval(prm.Name)
Next prm
Set rs = qdf.OpenRecordset()

Set qdf = Nothing
 rs.MoveFirst
    Do While Not rs.EOF

Open in new window



Sql-Server.png

Ms-access-Json.png

Regards

Chris
0
Hi

I am getting the following error in the image.If I remove the <>5 I still get it

Image1.png
0
What is wrong with this:

Set rstAdd = CurrentDb.OpenRecordset("Select * From tblEstimates Where EstimateID = =DMax("[EstimateID]", "tblEstimates")
0
Hi In the following query behind an Access combo box I want to limit the FirearmTypePK (Image1) to the selection made in another ComboBox  (Image 2)
How do I do that?

 1
2
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.