[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

Need to build an Iif statement based on values coming up 0 to not show.

I have the following control source in a text box:

=Sum(Nz([01WorkHours],0))

Open in new window


But there are some values that come up 0.
I want to change this to where if it's 0 - then come up blank even thought it's a numeric field.

I know I can use Iif statement to make this work but having trouble with the syntax:
I used this and get the #error

iif(Sum(Nz([01WorkHours],0)) = 0, Null)

How do I change the above to make it work?

Thank you in advance.
0
Hello All,

I up-sized a 2010 mdb Front-End to 2016 Accde but the Back-end is still mdb format.
it was working fine for four weeks.
Now my customer calls me that they are loosing Detail records from the Invoice table.
i checked and checked for any possibilities that we might be changing that can cause to delete records.
But i don't see anything, i also checked the Advanced Options it's the same as the 2010, Record level-locking etc.
Does anybody have an idea what it could be because it's a big problem?
Thanks in advance
0
Hi,
MS Access 2013, Windows 10 set up
I'm trying to export a report to Excel by right-clicking in the body of the report & selecting "Export", "Excel" but I'm getting the error "There is an invalid use of the . (dot) or ! operator or invalid parentheses". I'm attaching a screen-shot of the error - can anyone help please?Invalid use of (dot)
0
We are having an issue with a MYSQL database issue.  We created a front end in MS access that interfaces with a MYSQL data base.  The access tool works on multiple PCs throughout the organization except for a couple. When Installed on specific laptops the database kick back a credentials error that says: "Login Failures for the entered credentials.  (1 of 3 tries)"

We have installed the database on other computers and she can log in with those same credentials that kick back the error on her laptop so we don't beleive there is an issue with end users credentials in MySQL workbench.

"Login Failures for the entered credentials.  (1 of 3 tries)"
0
How to make Microsoft Access subform adjust it's height and bring footer info closer to last record?

Please note the following pics - that shows a timesheet where one sample image shows only two records and another shows multiple records where you have to scroll vertically.
I want to adjust the height of the subform so that the footer info always shows directly below the last record:
Now I can manually do this by grabbing the height of the subform if there is only one record, two record, etc... but that would be a lot of work.  And look at the total number of records then adjust the subform accordingly but is there not an easier way?

Thank you in advance for pointing me in the right direction!
ee-sample1.PNG
ee-sample2.PNG
0
Hello,

Attached is a sample database with an employee table and query which shows a reporting hierarchy.  What I would like is a query to see a list of managers and their count of direct reports.  So ideally I could see which supervisors have more than a certain number of direct reports by position.  Any assistance would be appreciated.

Thanks!
Test.accdb
0
I have several MS Access applications that use Global Constants to store paths and file names for report templates, save routines, etc. I must now modify these applications so that in the event of a "disaster recovery" scenario, the application can be accessed through Citrix. All the paths to files or folders will change if this is required, so I want to have a single parameter I can toggle so that when the application loads, the globals (now variables rather than constants) can be populated depending on whether the app is running normally on the server, or on the Citrix platform.

I'd like to use a reference table to store BOTH the VARIABLE NAME and the VALUE to place in the variable. Normally in code I would assign the value to the variable explicitly in code, e.g.

modGlobalStringVariable = "Z:\Path\Subfolder\Template.xlsx".

What I would like to do is store BOTH the variable name "modGlobalStringVariable" AND the value "Z:\Path\Subfolder\Template.xlsx" in a reference table. Then I can just pull a record set from the reference table and loop through it, assigning the variable name AND value once each time through the loop, e.g.

Do Until rsVariableNames.EOF
     strVarName1 = rsVariableNames!VarName
     
     strVarName1._______ = rsVariableNames!VarNameValue

     rsVariableNames.MoveNext
Loop

My question: What is the correct VBA Syntax to achieve this? The "________" is analogous to if I was using fields to populate a record set from a reference table, …
0
has anyone every attempted to store an access application in a document management system such as sharepoint? My initial reaction was 'that would never work', but our IT are trying to migrate data from  traditional file servers to sharepoint, but I wanted some insight into will an access application ever work in a system like sharepoint, or access databases accessed by numerous staff work in general? I am hoping the answer is no so I can suggest it isnt worth even investigating.
0
Need help with making Microsoft Access datasheet form act like Excel when you press up and down arrow keys
It's a timesheet database where columns txt01WorkHours - txt07WorkHours - are the hours per day.

If they happen to be in column 2 - txt02WorkHours and they press down- it should go to the next record down and that particular column.
How do I make the datasheet perform that action and behavior?

I know I have to implement the following coding and make the adjustment within the case vbKeyDown

What I don't know is how do I have the cursor go to the row below using VBA?

Private Sub txt02WorkHours_KeyDown(KeyCode As Integer, Shift As Integer)
  Select Case KeyCode
    Case vbKeyUp
        MsgBox "Up Arrow Pressed"
    Case vbKeyDown
        MsgBox "Down Arrow Pressed"
    Case vbKeyLeft
        MsgBox "Left Arrow Pressed"
    Case vbKeyRight
        MsgBox "Right Arrow Pressed"
End Select

End Sub

Open in new window

0
Basically I'm making a timesheet program that has hours for each day of the week with the last column totaling hours per project.

In the main form - I need to make it do a complete totals for that last column but I forgot how to do this at all.
In the text box below the datasheet I got:


But when I try it all I get is the #Error message.  What syntax do I need to change this in order for the totals to show?

=Sum(Forms([sFormName]).[sfTimeSheetWeekly].[Form].[txTTLHRS])

Open in new window

0
Hi

I am looking for a Access VBA script, to password protect (with Read only access) an excel file in a folder.

Please let me know for any queries.

Thank you
A
0
Upgraded to Access 2016 with Office 365 installed, now cannot make any changes to tables in DB, e.g edit or new record is greyed out

Even if I created a new DB and table I cannot add or edit records
0
Hi Experts

I am using Redemption to send emails from an Access 2003 database. This has been working fantastically...until now. This morning when trying to send emails we all get the circle of death and have to kill the program. I then went on to purchase the latest Redemption version thinking this will fix the problem. This however made it worse and threw errors when trying to open the database.

Does anyone have any experience using Redemption and can help me with fixing this problem?

Thank you

Adrienne
0
VBA Textbox not calculating

Private Sub Textbox3()
Me.Textbox3 = (IIf(Me.Textbox1 = "", 0, Me.Textbox1) + 0) + (IIf(Me.Textbox2 = "", 0, Me.Textbox1) + 0)
End Sub
0
I have a main form with a field named txtAverage.  Then on a sub-form in the main form I have a field named txtAverage which is a calculated numeric value.  The after update event of the subform's txtAverage needs to be reflected in the main forms txtAverage.  But it is not happening.  Here is the afterupdate code of the sub-form:


Forms!frmMainForm.txtAverage = Me.txtAverage

Note:  The field on the main form is a bound control so I have to control it with VBA code of the sub-form, right?

What am I doing wrong?
0
Hi, assuming the query has 10 fields, how do I create a delete query to delete a row only if detected either field1 or 2 or 3 are blank. Criteria with is null to all three fields in delete query is not suitable because fields 1 or 2 may contained data.
I can create 3 separate delete queries for field1 to 3 with is null to each field respectively, but I think there is a better way of doing it by single query, can anyone help thx.
0
Hi every one, I need to create a new record via button on the form will copy details and paste it for the new record, I’m using the code below, It doesn't paste anything,  it’s doing the copying but it’s copying the form controls names as well, I pasted the content of the clipboard in excel sheet and there was 2 rows the top row having the names of form controls instead of the fields names and the bottom Row have the copied record well but not in order,

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdRecordsGoToNew
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdPaste

Also I used the command Button Wizard still doing same thing
0
Dear All;

See how you can assist on the issue below:

Background

We place purchases orders to vendors every month, now it is the policy of management to match the goods received note with the purchase order so that in the event where some material are not received as per purchase order management can easily track that information. Therefore , I'm required to create a VBA code to do the matching so that those unfinished  purchase orders can be properly managed.

Here I want to be matching the Purchase order value vs goods received note value so that those that completely  received should show zero and those not finished show the remaining balance any Idea?

Regards

Chris
0
Access making a login form to access the database and forms
The following code is throwing an error of Run-time Error 13 mismatch and highlights the following in my code
I think this error has something to do with not having the proper relationship So how would I set up the relationship between the 2 tables listed below to make the code work is my question? I thought I needed a relationship between Security Level and UserSecurity
UserLevel is written into the tables as follows

tblSecurityLevel
SecurityID  SecurityLevel
1                    Admin
2                   User

tblUser
UserSecurity
Admin
User

Private Sub Command1_Click()
Dim UserLevel As Integer
If IsNull(Me.txtLoginID) Then
    MsgBox "Please Enter LoginID", vbInformation, "LoginID Required"
    Me.txtLoginID.SetFocus
ElseIf IsNull(Me.txtPassword) Then
    MsgBox "Please Enter Password", vbInformation, "Password Required"
    Me.txtPassword.SetFocus
Else
    'process the job
    If (IsNull(DLookup("[UserLogin]", "tblUser", "[Userlogin] ='" & Me.txtLoginID.Value & "'  And password = '" & Me.txtPassword.Value & "'"))) Then
        MsgBox "Incorrect LoginID or Password"
    Else
    UserLevel = DLookup("UserSecurity", "tblUser", "UserLogin = '" & Me.txtLoginID.Value & "'")
    DoCmd.Close
    If UserLevel = 1 Then
        'MsgBox "Login Successful"
    DoCmd.OpenForm "Mgmt Form"
    Else
    DoCmd.OpenForm "User Form"
   
    End If
    End If
    End If
End Sub
0
Lookup Error when search value is not foundDrop Down LookupHi,

  In my  ACCESS database, I have a table where I store company names and passwords.
 and form where I have two records - "AAA" and "CBS" . When searching for the record, if I enter non-matching value, then I get an error.
  Getting an error is ok, but it does not have what I call "graceful exit".
  For example, if I am typing "ab" and press [enter], I get an error message (as seen in the screenshot).
  What kind of visual basic code can I use to provide a graceful exit?

Thanks.
0
Hello All. I am trying to send data from a MS Access Query to a MS Excel Workbook.  
I am getting Error Message 3011, saying it can find the query.  The name is a match.  I copied and pasted the name into the code.
This creates and Open the Excel Workbook "Results"
All of the code works except the "DoCmd.TransferSpreadsheet acExport, line of code                                                                  
I have attached a photo of the Error Message.

Dim rs As Recordset
Dim Xrow, Xcol, rowCtr As Integer
Dim ObjXL As Object
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
   
strCurrentDrive = Left(Application.CurrentProject.Path, 3)
strCurrentPath = Application.CurrentProject.Path & "\"
strPath = Application.CurrentProject.Path & "\"
 
Dim exportFile As String
exportFile = strCurrentPath & "Results.xls"
If Len(Dir$(exportFile)) > 0 Then
    On Error Resume Next
      Kill exportFile
    If Err.Number = 70 Then
        MsgBox "Please Close the Current Results.xls Workbook"
        Exit Sub
    End If
End If

Set ObjXL = GetObject(, "Excel.Application")
Set ObjXL = CreateObject("Excel.Application")
Set objWkb = ObjXL.Workbooks.Add
Set objSht = objWkb.Worksheets(1)
'objSht.Activate
ObjXL.Visible = True

ActiveWorkbook.SaveAs FileName:= _
strCurrentPath & "Results.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

strSourceWorkBook = ActiveWorkbook.Name
    …
0
i have a table:
Rec      SA                      ISVC                     Desc                                     Type
1      48686882      8363304                    AIR CONDITIONING                    ELECTRIC                      
2      48686882      8363304                    SPACE HEATER                        ELECTRIC                      
3      48686882      8363304                    WATER HEATER                        GAS                
4      46666662      8555554                    AIR CONDITIONING                    ELECTRIC                      
5      46666662      8555554                    SPACE HEATER                        ELECTRIC                      
6      46666662      8555554                    WATER HEATER                        ELECTRIC


there are six records.
two groups :
first three and bottom three
I need to count ISVC where all measures are electric. the number is not 8363304, because one of the measures is gas.
so the right answer should be 1 , because for ISVX 8555554 every single measure is electric

could you help me to write a query to get
0
Is it possible to use a Speech to Text program to enter data into text boxes in Access?  

If so which one has anyone used?
0
I need the correct syntax for a Microsoft Access VBA set of code that would allow a main form - requery a subform's information.
This used to work for me in the past (Access 2000-2013) but I'm using Office 365 now.

Forms!fMainMenuTimeSheetMonthly!sfTimeSheetWeekly.Form.Requery
Forms!fMainMenuTimeSheetMonthly!sfTimeSheetWeekly.Visible = True

Open in new window


Primarily I have a lot of Me.Controls("txtBox1") in the subform to control from the main form.

This works within the subform itself:

Me![sfTimeSheetWeekly].Requery
Me![sfTimeSheetWeekly].Visible = True

Open in new window


But I need to manipulate it from outside the main form.
0
Hi Experts,
I have a linked table to CSV file, and after finally finishing defining all field types and linking (hundreds of fields all different types, dont ask why...), I realized file was not named correctly.
Now just trying to change the name of file and getting the attached error.
Can someone please help me out here, so I dont have to redo the entire process...
Thanks
Untitled.png
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.