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

I would like to create a button to create a record and input today's date on a field.
would you advise how to set a script on Macro tool?
0
Is there an easy way in access, to extract text mid-string and populate it into a new field in a table. I have a table with an excess of 1,000,000 rows, and one field (FullName), is essentially an UNC path from a file server, e.g. \\server\share\username . I basically need to extract the username section from that into a separate field as I need to do some group by and min/max stats on the field. There is a slight inconsistency in that some rows will have additional data in the path e.g. \\server\share\username\folder\doc1.docx, whereas at the start the rows are just \\server\share\username  - its basically a recurse file inventory which would start with the usernames, then a more detailed breakdown of the files held within each simultaneously afterwards - but either way, I need to extract the username 'segment' from the string on every single row, regardless of format and wondering how best to do so.
0
I have a query trying to create a record, but it doesn't work.
another question is, how to set a modified date field.
Please take a look on sample file.
Database2.accdb
0
Hi

I want to evaluate the sub reports in my current & prior year income statements so that the accounts lines on them can be compared properly line by line in Ms Access VBA:

Condition 1

If the current year sub report ([Reports]![srptRevenueRevenueOnlyYTD]![SumOfTotal]) has some figures but the prior year sub report is empty ([Reports]![srptRevenueRevenueOnlyYTDS2]![SumOfTotal]) then the prior year must return same lines with ZEROS

Condition 2

If the prio year sub report ([Reports]![srptRevenueRevenueOnlyYTDS2]![SumOfTotal]) has some figures but the current year sub report is empty ([Reports]![srptRevenueRevenueOnlyYTD]![SumOfTotal]) then the current year must return same lines with ZEROS

I have tried the code below its not working it keeps on asking for debug.
Private Sub Report_Load()
If IsNull([Reports]![srptRevenueRevenueOnlyYTD]![SumOfTotal]) Then
[Reports]![srptRevenueRevenueOnlyYTDS2]![SumOfTotal] = Null
Else
[Reports]![srptRevenueRevenueOnlyYTDS2]![SumOfTotal] = 0
[Reports]![srptRevenueRevenueOnlyYTDS2]![SumOfTotal] = [Reports]![srptRevenueRevenueOnlyYTDS2]![SumOfTotal]
End If
End Sub

Open in new window


Regards

Chris
0
Hi Experts,

I'd like to know if there is a way to copy an Excel sheet to a new workbook without having to open it? My source file is a xlsm and when I run my macro, a pop-up keep appearing. Is there a solution to this?

Sub Sheet_SaveAs()

Dim wb As Workbook

Sheets("Output").Copy

Set wb = ActiveWorkbook

With wb
.SaveAs ThisWorkbook.Path & "\ - Recon_Output_ " & Format(Date, "yyyymmdd")
.Close False
End With

End Sub

Open in new window


Thank you.
pop-up.png
0
Hi

My Access database suddenly increased from 3MB to 13MB. I stripped out half the forms and did a compact and repair but that didn't help.
Is there anything else I can do?

Thanks
0
Hi, how can I run MS-ACCESS Application in a Windows machine with No Ms-Access installed?
0
Hi,  How can I start an MS-Access application not showing the ms-access initial prompt?  I mean, I don't want to show the ACCESS initialization or start up screen, I want to hide that.
0
Hi

I am using a continuous form.

DoCmd.GoToRecord , , acLast:     gets you to the last record.

But, when  I programmatically add a record on my continuous form  and use this command,  the only record that I see  is the last record.

I would like to see (by example) 10 records above the last record.
0
Hello while trying to create a ribbon, I am getting the following error.  Attached is the error message and below is the xml  i have in the UsysRibbons table.

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customUI">
  <ribbon startFromScratch="false">
    <tabs>
      <tab idMso="TabCreate" visible="false" />
      <tab id="dbCustomTab" label="A Custom Tab" visible="true">
        <group id="dbCustomGroup" label="A Custom Group">
          <control idMso="Paste" label="Built-in Paste" enabled="true"/>
        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>
Picture1.png
0
I would like to merge multiple tables in MS Access.
I have 13 tables with same data fields.
How do I append 13 tables to 1 table?
0
I am using a MS Access 2003 database on a NAS.  Its working fine but sometimes the application icon I specified in the DB startup options doesn't always display.  If I close and reopen the DB it does.

Is there anyway I can force a refresh to read the application icon after the DB is opened? (VBA code on when a form opens for example.)
0
SELECT tblSupplier. *
FROM tblSupplier;

Open in new window


If I create an Ms access query like that it means that whatever field I create will be on the query no matter what, the same goes for deleting on the opposite.

Question 1

What should I do to achieve the same behavior on the report, example I DO NOT want to be adding the new fields on the report manually whenever a new field is created, it should be automatically appear on the report without being added manually as it were on the query?

Question 2

Since such type of a query design does not allow filters such that blank fields should NOT TURN UP on the report, is there a way to do the filters at report level to avoid blank field & Zeros?

Sorry to bother you I’m not a full-time trained IT person, I’m a qualified Chartered Certified Accountant, I have only talk myself programming because of the nature of my job. For example, its not possible to competently audit an IT project if one has no clue about simple IT design & programming, that is the reason why I’m here.

Kindly take the above into account.

However thank you to all of you , you contributed a lot to my learning process.

Regards

Chris
0
Hi

I am using the following Access VBA code and for some reason no updates are hitting the database. I a getting no errors.
I have included the SQL held  in the variable S further down

  Dim S As String
    S = "Update t_Licence Set LastRenewalDate = #" & Me.RenewalDate.value & "#"
    S = S & " And ExpiryDate = #" & Me.ExpiryDate.value & "#"
    S = S & " Where LicenceNumberPK = " & oLicenceNumberPK
   
    CurrentDb.Execute S

Update t_Licence Set LastRenewalDate = #7/11/2019# And ExpiryDate = #6/11/2019# Where LicenceNumberPK = 2
0
Hi

I have an Access ComboBox  with the name Manufacturer_Combo and the record source SELECT t_Manufacturer.Manufacturer, t_Manufacturer.Country
FROM t_Manufacturer;
I have a text box with the control source =[Manufacturer_Combo].[column](1)
I am getting an error #Name? in the text box and I can't work out why
0
I would like to create a button on MS Access form, which set a filter certain by certain keyword.
and another button to removed the filter
0
add text to textbox based on  value chosen in optionbox
frame210 = frame optionbox
option212 = no
option214 = yes
REASON_FOR_ESCALATION =  textbox entry

If the option "Yes" = true then allow entry into textbox REASON_FOR_ESCALATION
if option "No"  = true then  REASON_FOR_ESCALATION.Enabled =  FALSE AND default Text =  "Seller Did Not Provide"

i cant get the sequence correct to do one or the other.

The MsgBox is just an extra caution that they are overwriting data in the box.


Private Sub Option212_GotFocus()
' Is this a business case  NO option
Dim answer As Integer
answer = MsgBox("You are about to override the Text in this box! Do you want to Continue?", vbQuestion + vbYesNo)
 
  If answer = vbYes Then
       ' MsgBox "Yes"
       Me.REASON_FOR_ESCALATION.Enabled = True
      Me.REASON_FOR_ESCALATION.SetFocus
       Me!REASON_FOR_ESCALATION.Text = ""
       Me.Frame210.Value = 2
    Else
       Exit Sub
  End If
  
 End Sub

Private Sub Option214_GotFocus()
' Is this a business case YES option
Dim answer As Integer
answer = MsgBox("You are about to override the Text in this box! Do you want to Continue?", vbQuestion + vbYesNo)
 
  If answer = vbYes Then
       ' MsgBox "Yes"
       Me.REASON_FOR_ESCALATION.SetFocus
       Me!REASON_FOR_ESCALATION.Text = "Seller Did Not Provide"
       Me.Frame210.Value = 1
       Me.REASON_FOR_ESCALATION.Enabled = False
    Else
       Exit Sub
  End If
End Sub

Open in new window

0
Access Form Design issue, I have a Pivot Table I need to build as a form but I'm unsure how to get my columns and rows like that of the Pivot table attached to build it in the form...Sample data...A crosstab query will not do the job and I'm not that crafty with Transform or UNION....I have attached the sample Spreadsheet with Pivot Table and data for any suggestions or samples on how to concur this is greatly appreciated....Thanks
Copy-of-Copy-of-QryTest.xls
0
Each time i use a split screen, many elements of the first data row is unreadable. I first have to click into the row and then the data is shown. Pls see attachment. In the example Betrag and Buchungstext are not shown (also they do exist). Thank you so much for any help. Rg Michael Screenshot.png
0
I have an application where I run some simple sql execute statements.  here is an example of one:

currentdb.execute "Update databasebuttons set pagecaption = 'Agency Evaluation',buttonlabel ='Agency Evaluation' where Buttonnum = 1001",dbseechanges

This syntax has worked fine in Access versions 2007 through 2016.  Now in Access version 2019 it throws the error 3340 "Query is corrupt"

What's the deal?   What is the solution to resolve the issue where this will continue to work in 2019 but also work in 2017 - 2016 version?
0
Can I access an Access Database via a website? If so, could anyone advise how I do this?
0
Just for consultation purpose , I want to maintain the in-belt Ribbon preview button and at the same time adding my excel export other programs export function in my ms access app.kindly see whether this is okay with you.

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon> 
<tabs>
            <tab id="grpRprt1" label="Export Reports">
                
                <group id="grpRprt3" label="Export">
                    <control idMso="ExportExcel"  visible="true"  size="large" /> 
                    <separator id="sprtrRpt1" />
                    <control idMso="ExportWord" visible="true"  size="large" />
                    <separator id="sprtrRpt2" />
                    <control idMso="ExportTextFile"  visible="true" size="large" />
                    <separator id="sprtrRpt3" />
                    <button idMso="FileSaveAsPdfOrXps" visible="true" size="large" />
                </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>

Open in new window


I have also noticed that the export is using 97 to 2003 is it possible to change it so that it starts from 2003 to 2016?

Regards

Chris
0
Hi

I am using the following query to get all the Action Types in a ComboBox when the user selects an Arm Type in the ComboBox above but for some reason it isn't working

1
0
Hi
I am trying to refer to a field called LastName in my subform called frmLicenseeList using frmLicenseeList.Form.LastName
It is nor working
What am I doing wrong?
Thanks
0
moving first moving last in code  : code revision
In the code below, since i'm getting the max VALUE in the sql.
Is  is necessary for me to use,
r.MoveLast
r.MoveFirst

and i'm not using the tp  = r.recordcount


StrSqlC = "SELECT Max(dbo_t_nsc_trackcode_assigned_DataEntry.NSC_Id) AS MaxOfNSC_Id " & _
"FROM dbo_assigned_DataEntry" & _
" WHERE (((dbo_assigned_DataEntry.ID_Racfid)=  '" & str & "' ));"
             Set r = CurrentDb.OpenRecordset(StrSqlC, dbOpenDynaset, dbSeeChanges)
r.MoveLast
r.MoveFirst
tp = r.RecordCount
r.MoveLast
MaxId_csp = r.Fields(0)
r.Close
Set r = Nothing

Open in new window

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.