Microsoft Access

221K

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'm trying out Azure SQL database for the first time.  The migration and default connection from the office went fine.  However, the db is a BE to an Access FE.  Access needs to connect from remote locations without fixed IP addresses.  I would also like Access to use a login that is not the server login.

Again, I'm brand new to Azure.  After reading until my eyes nearly popped out, I understand that Azure supports a contained database and I "should" be able to set up users directly in the database security without giving then access to other databases.  I get an error trying to connect using this test user account from both, within and outside the office.

From outside .the office I can't even connect my server admin account.  I get an IP error even though I created a firewall rule in the database to allow all IP addresses.

Both of those above are within a particular db, not the server.

Does anyone have any ideas?
0
I want to find the Last Row and Last Column of an Excel Spreadsheet via VBA in Access without using the MS Excel 16.0 Reference
This is what I am using the get the Last Row and Last Column with the reference.

               
lLastRow = oExcelWrkSht.Cells(oExcelWrkSht.Rows.Count, "A").End(xlUp).Row
                lLastColumn = oExcelWrkSht.Cells(iCols, oExcelWrkSht.Columns.Count).End(xlToLeft).Column 

Open in new window


Normally this wouldn't be an issue but I have users that don't have Excel 2016 loaded on their system.
I have no idea why it is not using the Excel 2016 or 2013 in most of the users case reference.  But it is not.

Thanks john
0
Hello,

I have a form and subform.  After a requery, when I am not able to move to the bookmark.  I am getting an error "Not a valid bookmark".  Please advice.


        Dim sf As Access.Form
        Set sf = Forms![F_Maint_Check_Main]![F_Maint_Check_Vol_SoftAR_EC_New].Form
             
             Dim value As Variant
              value = sf.Bookmark
                   
                     sf.Requery

                         sf.Bookmark = value
0
Access office 365

Is there a way to
Setting the recordsource property on a subform without it requerying the subform ?

I'm doing this..when a form opens up on LoadEvent.

Me.dbo_t_Sourcing_Quote_Detail_subform.Form.RecordSource = ""
Me.TD_Material_V_subform.Form.RecordSource = ""


Later in the code I'm doing this:
Me.dbo_t_Sourcing_Quote_Detail_subform.Form.RecordSource = "t_Sourcing_Quote_Detail"
Me.TD_Material_V_subform.Form.RecordSource = "td_material_v"

But dont need it to show results for requery yet ?

Thanks
fordraiders
0
So I am exporting data from an Access 2013 Query to an Excel 2013 Template.
The export works out great.

The issue I am running into is wrapping text on a select range after the export via VBA from Access.
I can wrap text on the whole sheet just by doing the following:
oExcelWrkSht.Cells.WrapText = True

Open in new window


My starting row will always be 14 (A14) and my ending column will be 14 (M14).
But the range can be anywhere from 6 to 600.

I have added code that tell me the last row used:
lLastRow = oExcelWrkSht.Cells(oExcelWrkSht.Rows.Count, "A").End(xlUp).Row

Open in new window


Which on my test is 30.

So I want all cells between  A14 and M30 to be wrapped.
I tried:
oExcelWrkSht.Range(lStartRow, lLastRow).WrapText = True

Open in new window


And I get an error, 1004

Thanks for any help

Attached is the full code.
Excel-Export.txt
0
Hi all, i have continuous  form with rate and quantity text boxes so sometimes values can be null as well
i have button total cost so when user clicks on that button it needs to display sum(rate*quantity) of all the rows
i tried using vba code on click event of that button as
Me.button.Value = DSum(Rate * Quantity, "tablename", "tableid = me.id.value")
but when i run this id = me.id.value is taking null value
any idea on this how to get that total cost ignoring null values
tableid = me.id.value is criteria i am using where tableid is column in table and id is textbox value(this value in not null)  in my form

FYI atm to display that total cost in the footer i am using unbound textbox where controlsource is expression =Format(Sum([Rate]*[Quantity]),"Currency") which is working fine
but now user wants to display that value on click of a button
thanks in advance
0
Hello,

I have a form and subform.  After a requery, when I try to gotorecord in the subform, sometimes the record is in the bottom of the form.  Below is my code... how do I always have the record show up on the top.

Dim sf As Access.Form
        Set sf = Forms![F_Maint_Check_Main]![F_Maint_Check_Vol_SoftAR_EC_New].Form
           
            Dim value As Integer
            value = sf.CurrentRecord
                               
                sf.Requery
               
                   DoCmd.GoToRecord , , acGoTo, value
                       DoCmd.Close acForm, "F_Maint_Master_Edit_Button", acSaveYes
0
I am using MS Access

1. I have a main form (MainForm).  
2. I have a sub form (SubForm).  
3. From the SubForm I open another form Form2.  
4. I would like to display the currentrecord in Form2 in a textbox so I can use it to GoToRecord in the SubForm.
0
Hi, i have a form where we pass string say aaa bbb ccc through inputbox function and enter a value say 2
then in the text box it needs to display bbb as we entered value 2.
if user entered value 3 then it needs to display ccc
i tried some vba code but it didin't get me desired value
  Dim i As String
   i = InputBox("Enter Values")
   Dim j As Integer
   j = InputBox("Enter number")
Me.txtbox.Value = Mid(i, j)
 this is returning aa bbb ccc in the box
i tired using split function as well but didn't got required answer
can someone help me with this code
thanks in advance
0
Update table with common values in two tables:


SELECT ENERDEQ.[Lease Name]+'  '+ENERDEQ.[Well Num] AS WELLNAME,
              ENERDEQ.[Spud Date],
              ENERDEQ.API, ENERDEQ.[Operator Name],
              ENERDEQ.Latitude,
              ENERDEQ.[BH Longitude],
              ENERDEQ.[Lease Name]

FROM

ENERDEQ INNER JOIN ariesadmin_AC_PROPERTY ON (ENERDEQ.[Operator Name] = ariesadmin_AC_PROPERTY.OPERATOR) AND
(ENERDEQ.Longitude = ariesadmin_AC_PROPERTY.LONGITUDE) AND
(ENERDEQ.Latitude = ariesadmin_AC_PROPERTY.LATITUDE) AND
(ENERDEQ.[Well Num] = ariesadmin_AC_PROPERTY.WELL_ID) AND
(ENERDEQ.[Lease Name] = ariesadmin_AC_PROPERTY.LEASE)

WHERE

ENERDEQ.[Lease Name] = AC_PROPERTY.[LEASE] AND
ENERDEQ.[WELL NUM]=AC_PROPERTY.[WELL ID] AND
ENERDEQ.[LATITUDE]=AC_PROPERTY.[LATITUDE] AND
ENERDEQ.[LONGITUDE]=AC_PROPERTY.[LONGITUDE] AND
ENERDEQ.[OPERATOR NAME]= AC_PROPERTY.[OPERATOR];

This query has error, I would appreciate suggestion.

Thank you,
0
I have a MS Access form using VBA for a field after the update.  The code is checking for a character in another field to determine what code is executed:

   
    If InStr(Nz(Me.LNotes, 0), "ß") = 0 Then
        Me.LastStatus = Now()
    Else........

All is working fine however I just noted that if the field (LNotes - memo field) has the word "Processing" the initial code in the IF statement is NOT executed even if the character ß is not present.

Any idea why this might be?  I have been using this code for years.
0
I have 8 sub-reports on a main report.  I'm showing two examples in design view here.  The bottom one appears fine but the top one is strange.  Why does the top one look like it does?

SubReport Examples
0
I need to know if i can pass a string to a stored procedure in sql server ?

 I have a small routine that i use in Access vba to build a where statement
The values come from an Unbound textbox on a form

Nf1 = ""
cD = ""
cD = Me.Text0    'unbound textbox called  "text_search" 
    lk = "Like"
    lm = "text_search"
 
    cD = Trim(cD)
    
    ' how many items are in the cell ?  build an array
    cArrSql = Split(cD, " ")
        
    ' search through the cell requests tokens.
    For x = LBound(cArrSql) To UBound(cArrSql)
        If x = LBound(cArrSql) Then
            Nf1 = "WHERE " & lm & " Like " & Quote("%" & cArrSql(x) & "%")
        Else
            Nf1 = Nf1 & " AND " & lm & " Like " & Quote("%" & cArrSql(x) & "%")
        End If
    Next
 
    BuildWhere = Nf1

using this for single or double quotes:
Function Quote(ByVal strText As String)
  '  Quote = Chr(34) & strText & Chr(34) ' double
    Quote = Chr(39) & strText & Chr(39)  ' single
End Function

Open in new window


I may have only 1 value or sometimes many values typed in text0:
i.e.  Glove  XL
I.E.  12324   HBO

What I need:

I need to pass this "where clause" as a string to a stored procedure to sql server someway ?

The where clause when built looks like this :

Where text_search like  '%guardian%'  and text_search like '%481361%'  


I want to pass to sql server:
@string   text_search like  '%guardian%'  and text_search like '%481361%'

My procedure in sql server is:   a basic select statement
select       [EstimatedFreightCost]
      ,[FreightOverrideFlag]
      ,[ForwardingAgent]
      ,[text_search]
  FROM [ss_prog].[dbo].[Quote_Detail]
 
  where  + @string +
 
  (this is not working)
 
 

Thanks
fordraiders
0
I have installed the module which is required to be part of Ms Access project in order for the code below to work , I have tried to compile the code below after referencing to MICROSOFT SCRIPT RUNTIME all is fine NO errors

Here is the VBA code that deserializes the JSON text.

Public Sub Example()
 
 Dim JSONString As String
 JSONString = "{""Name"":""Value""}"
 
 Dim JSON As cJSON
 Set JSON = New cJSON
 
 Dim D As Dictionary
 Set D = JSON.Deserialize(JSONString)
 If (JSON.IsOk()) Then
 MsgBox D.Item("Name")            'shows Value
 Else
 MsgBox JSON.ShowWhyNotOk()
 End If
 Set D = Nothing
 
 Set JSON = Nothing
 
 End Sub 

Open in new window


What I want is to send the following information from the controls from tblcustomerinvoice and tblinvoicedetailline:

(1) Customer Name (String)
(2) Address (String)
(3) Invoice Number (Integer)
(4) Quantities (Double)
(5) Vat (Double)
(6) Discount (Double)
(7) Price (Double)
(8) TotalPrice (Double)

I want to use to Json language as a way of transferring  data from Ms Access VBA to any system done in Java programming language

How do I use the above code to move data to the requesting program at the time when people are pressing the SAVING BUTTON
0
Hi Guys, There must be a better way to move controls at runtime.
The question is how I reposition a textbox or label control at runtime. I’m using Access 2010.
I have a 7 reports that do roughly the same thing that is they prints 3 rows, a label, a quantity and a cost, in the details section of the report.
Please the layout attached

 

If say the 3rd and 4th column is empty I need that layout to as below so in this case the 5th column gets moved the 3rd column position.
 
I’m using the reports Detail_Format event to trigger the code.
I’m not going in detail at this point because I’m hoping someone has a simple solution.
But I’m drowning in code.

Regards
cornholme
Three-FiveColumns.png
0
I have Ms Access 2016 on my machine as the only program , now I want to buy the other office licence for 2016 for purpose of having a complete office suite.

Is it possible to install the additional office suites , like excel , word & power-point to the current or existing office with Ms Access 2016? I need these programs for audit purpose and presentations.

If its not possible what is the way forward?

Regards

Chris
0
Access Error: Class does not support automation or does not support expected interface

Sub PasteNewGeogData()

Dim xlWB As Object

Dim strPath As String
Dim strFileName As String

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Integer
Dim fieldValue As Long
Dim xLSheet As Variant
Dim qdf As DAO.QueryDef

On Error GoTo After_Error

strPath = "\\Mac\AAIM\Portfolios\"
strFileName = "GraphAccessGeogData.xlsx"
Set xlWB = Nothing

If IsValidOLELinkEstablished(xlWB, strPath, strFileName) = True Then
 
        'Set worksheet
        Set xLSheet = xlWB.Sheets("Sheet2")
        xlWB.Application.Calculation = xlManual
        xlWB.Application.Screenupdating = False
        'Get reference to database and relevant query
       
        Set db = CurrentDb
       
        Set qdf = CurrentDb.QueryDefs("qry_UnitsByCountyForSelectedHA")
        qdf.Parameters("TempVars!HASelected") = TempVars("HASelected").Value
        Set rs = qdf.OpenRecordset()

       
        xLSheet.range("A1").copyfromrecordset (rs)  'This gives the error
0
hello team,

I am working on the below query and I am facing a bit of an issue with it. I have two criteria I need to meet, first one is working, the second one is giving me an error. I need to run records that the quick reg time minus the depart time equal zero. basically same day service. and +1 day and -1 one day

the whole query is:

SELECT tbl_note.MRN, tbl_note.EmployeeId, tbl_note.FIN, DateValue([DEPART_DATE_TIME]) AS Depart_date, tbl_note.Name, tbl_note.QUICK_REG_TIME, DateValue([QUICK_REG_TIME]) AS Quick_Reg_date, tbl_note.Note_date, DateValue([QUICK_REG_TIME]-DateValue([QUICK_REG_TIME]=0)) AS Expr1
FROM tbl_note
WHERE (((DateValue([QUICK_REG_TIME]))=([tbl_note].[Link_NoteDate])));

the part giving me the error is DateValue([QUICK_REG_TIME]-DateValue([QUICK_REG_TIME]=0)) AS Expr1
0
I have a field on the main form.  When I exit that field I want the tab to go to a specific field on the subform.  Here is my code but it doesn't work:

Forms!frmMainForm!subfrmSubForm.Form!txtAppointmentDate.SetFocus

What is wrong with this code?
0
Access - I need to run this query but not have any dups in my report.

SELECT tbl_PMs.PM_ID
FROM tbl_PMs INNER JOIN tbl_TechPMLink ON tbl_PMs.PM_ID = tbl_TechPMLink.PM_ID_FK
WHERE (((tbl_PMs.New_Crafts)="o"));
0
I want to use/refer to a Public Const (pubAccNo) in a textbox on a report (see example below) - but when running the report it asks for the value of the constant - can global constants not be referred to on a report ?

ControlSource = "Accountnumber: " & pubAccNo
0
hello experts,

I have 3 set of data, one set is about 5000 exam record, 2nd set is note detail records which is about 8000 records, and schedule records (Rota). the only common filed for the 3 set of data are employee ID and dates. I am planning to link the 3 tables using employee ids and dates. this way I can know the work for each staff. so if someone's record is showing in a day that he did not work, I can manually change to the date before. this seem a bit risky but this is for a monthly report that I run.

first table is Rota_tbl and the filed is Date_Shift and the format is dd/mm/yyyy and the data show as this 02/07/2019

second table is Exam_Tbl and the field is Exam_Date , format date / time, data shows 01/07/2019 2:01:54 PM

third table is Note_tbl and the filed is called Note_date and the data show as this 01/07/2019 5:01:22 PM

the confusing part is the date in the first two tables include time. I would like to convert the dates to dates only. the reason I am doing this is because some of the shifts may start on one date and end in the next date, if I group the data to run averages, max or min, the data will be be accurate because the employee may serve one customer in the overnight shift and this will be considered a work date. example below. I know there maybe a way to round back and forward based on type of shifts since the shifts are set by hours. I thought maybe this would be easier.....open for suggestions!

ID      MRN      FIN      GRADE      QUICK_REG_TIME      …
0
I have a filed that contain a name and employee number.  I import this data from excel to access. I would like to create a update query to separate the code from the name, the text filed look like this, Bob Helmond (ID 24177). I need to be able to extract the code of 24177 in a new column called employee id. it can be done after the data is imported. I don't want to include the word ID in the code. just the numeric number.

the filed currently is called name, the table is called, tbl_Exam,

thank you
0
hi i am getting error: else without if for following on click event code
can someone tell where i did wrong
 If Me.Dirty = True Then
    Dim blnSave As Boolean
    If MsgBox("Do you wish to save changes?", vbQuestion + vbYesNoCancel, "Save Confirmation") = vbYes Then
    blnSave = True
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.OpenForm ("frm_WorkOrderSearch")
   Else
   If MsgBox("Do you wish to save changes?", vbQuestion + vbYesNoCancel, "Save Confirmation") = vbNo Then
   Me.Undo
  DoCmd.OpenForm ("frm_WorkOrderSearch")
  Else
  Exit Sub
  End If
    Else
        DoCmd.OpenForm ("frm_WorkOrderSearch")
    End If

when i click on button it needs to check if form is dirty or not
if form is not dirty then close button should open other form
if form is dirty it needs to ask user to save changes or not
also it needs to have cancel button as well in case user wants to stay on same page
so for cancel button i wrote exit sub..is this correct way of writing code to do nothing and stay on same page
please guide me on this
0
Some users are getting the message Your network access was interrupted.  To continue close the database and then open it again.  We had it happen yesterday.  I did a compact and repair on the BE and rebooted the server last night and again this morning.

It is not happening to all users.  HELP!
0

Microsoft Access

221K

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.