Microsoft Access

219K

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

Access 2013 report "Conditional Formatting" is disabled. I've tried design view and also layout view with same results. How can I enable "Conditional Formatting" for a report?  Thanks!
0
Free Tool: ZipGrep
LVL 12
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Hi everyone,

I hope you can help with this...

I have the following SQL in a pass-through query to the Sage 50 stock table:-

SELECT STOCK.STOCK_CODE, STOCK.DESCRIPTION, STOCK.COMPONENT_CODE_1, STOCK.COMPONENT_QTY_1 FROM STOCK WHERE (((STOCK.COMPONENT_QTY_1)>0)) AND (((STOCK.COMPONENT_CODE_1) Is Not Null));

It's working correctly with "STOCK.COMPONENT_QTY_1)>0" but completely ignoring "STOCK.COMPONENT_CODE_1) Is Not Null" and showing empty fields. What am I doing wrong? I tried with a select query to start with, but I got an error message, the same as this user, which lead me to use a pass-through query instead. But now I'm stuck...

Thanks in advance,
Beckey.
0
I am trying to limit the list of a combo box based on a query. The combo box should contain a list of apartments that do not have matching fields between tblApt.AptID and tblRental.AptID.
In other words the apartments listed should only contain the apartments that are not rented.

In the attached DB if the query works properly it should not list apartments 1000-A through 1000-D.

You would see this in the form Create new Rental Switchboard Item.

Thanks,
Stuart
HCPV-Current.accdb
0
I am trying to create a way to add Vehicle Make and Model which makes sense. And am having trouble doing so.
You can see my preferred approach on things like "frmNewLeaseTerm" and  "frmNewState" Or on the form "frmNewLease" and clicking on the buttons for adding lease terms suffix or State.

I know this may not be the standard approach but it is the one I need as I need to make things extremely simple.

The problem I'm having is due to the fact that its using two tables "tblCarMake" and "tblCarModel"

My Goal would be to have it look like the attached picture but with an added button for "Add Model"
I also forgot how to open the form as a popup so if you could help with that as well I would be grateful.

Also I seem to be having a issue with the "New Tenant" button on the bottom of "frmNewLease" and also my dynamically updating combo boxes for make and model arent working right.

Thanks,
Stuart
Image-3.png
HCPV-Current_Backup.accdb
0
I'm having a brain cramp this afternoon.

I'm building a SQL Server connection string on the fly in an Access application.  If I use SQL Authentication, the string looks like:
Driver={SQL Server Native Client 10.0};Server=MyServerName;Database=MySchemaName;Trusted_Connection=Yes;

Open in new window

and works fine.  But if I uncheck Use SQL Authentication and enter a UID and PWD, the connection string looks like:
Driver={SQL Server Native Client 10.0};Server=MyServerName;Database=MySchemaName;User ID=myUserID;Password=myPassword;

Open in new window

and does not work, even though myUserID and myPassword are the same userid and password I used to log into my computer.  I have tried this with:
UID=myUserID;PWD=myPassword

Open in new window

but that isn't working either.

I know this has to be something amazingly simple, please help.
0
I have a user that needs to import data daily into Access from an Excel spreadsheet.
I've been using the importspreadsheet in my Access VBA, then dump that data into a temp table, and then import it using append queries.
The queries run from a form with buttons so the user doesn't have to do anything, everything is imported in the back with the VBA.
The problem I'm having is that there are about 50 fields that have relationships to other tables using a primary key. For example the spreadsheet has the Customer Name but in Access the customer has an ID number. Before I write a Dlookup for each field I was wondering if there is an easier way.
What's the best/fastest way to lookup the correct IDs from the other tables?
0
In Access, I am using a Form, "FORM1"  and Subform, "tbl_Customer_subform". I am using combo boxes and text boxes on my main form to filter and
search on the subform. The challenge I have yet to figure out is how to Refresh or requery the subform  that contains all the customer records back to
its original 5,000 records. Can I do this with a command button?  Below is the code I am now using for searching and filtering.  Is there a way to easily do this?
Thanks in advance for any help!
Rick


 Private Sub Combo4_AfterUpdate()
Dim MyWriter As String
MyWriter = "SELECT * From [tbl_Customer] Where ([Field17] ='" & Me.Combo4.Column(1) & "')"
Me.tbl_Customer_subform.Form.RecordSource = MyWriter
End Sub


Private Sub Combo23_AfterUpdate()
Dim MyCustomer As String
MyCustomer = "SELECT * From [tbl_Customer] Where ([Field3] ='" & Me.Combo23.Column(1) & "')"
Me.tbl_Customer_subform.Form.RecordSource = MyCustomer
End Sub


Private Sub Text28_Change()

Dim strSQL As String

strSQL = "Field3 LIKE " & Chr(34) & Me.Text28.Text & "*" & Chr(34)

Me.tbl_Customer_subform.Form.Filter = strSQL

Me.tbl_Customer_subform.Form.FilterOn = True
End Sub
0
I have an Access 2010 database and a command button with the following code:

  Dim stDocName As String
  Dim stLinkCriteria As String
  Dim strFindEval As String
  Dim strFindStaff As String
  Dim Staff As String
  Dim Evaluator As String
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim UserName As String
    UserName = Environ("USERNAME")
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    If UserName = "ABC" Or UserName = "DEF" Then
    strFindEval = InputBox("Enter Evaluator's Initials", Evaluator)
    strFindStaff = InputBox("Enter Staff Member's Initials", "Staff Member")
        stLinkCriteria = "[Staff]=" & "'" & strFindStaff & "'" _
        & " AND " _
        & "[Evaluator]=" & "'" & strFindEval & "'"
            GoTo Select_Form
        End If
    strFindEval = UserName
    strFindStaff = InputBox("Enter Staff Member's Initials")
        stLinkCriteria = "[Staff]=" & "'" & strFindStaff & "'" _
        & " AND " _
        & "[Evaluator]=" & "'" & strFindEval & "'"

Select_Form:
' Accounting
    If strFindStaff = "GAS" Or strFindStaff = "RJL" Then
        stDocName = "frmEvalStaffAcctDone"
            Else
        stDocName = "frmEvalStaffDone"
    End If


I need the code to use the data entered in the strFindStaff prompt and then see if this user is a member of the ACCOUNTING group.

I'm hoping to replace the following so that it looks to see if "GAS" OR "RJL" are members of a …
0
I am getting a Run-Time error 3131. Syntax error in FROM Clause.
I am using the following code in Access on a form using a combo box to select "Writers" in a subform.
I am not a programmer and can not seem to figure out what is wrong. Any idea why I am getting this error?

Code -
Option Compare Database
Private Sub Combo4_AfterUpdate()
Dim MyWriter As String
MyWriter = "SELECT * From tbl-Writers Where ([Field17] ='" & Me.Combo4 & "')"
Me.tbl_Customer_subform.Form.RecordSource = MyWriter
Me.tbl_Customer_subform.Form.Requery
End Sub


Thank you...
Rick
0
Dear All;
We have one issue with one of our superior, he wants an interface which was done in window form application using C# programming language opposed to the MS access interface done with VBA, according to him, VBA is too junior compared to C#. Correct me if I’m wrong:
(1)      VBA & C# code are very much similar, the only major notable differences are :
•      In C# we use curly blackest ({}) while in VBA they are not applicable
•      VBA works only along a program for example Excel, Word or Ms Access while C# work in windows not necessary a program
•      VBA uses Subs & Functions while in C# uses Functions
•      Variables C# are declared in one standard way while in VBA you opt to use Dim function, for example in C# an integer will be declared like ( int firstNumber ) if we want to store a number we will just now write like ( firstNumber = 12) while in VBA we will use Dim ( intFirstNumber as integer ) and then say intFirstNumber  = 12)
Now my question is how we are going to deploy an interface written in C # connecting to MS Access Back End through Sql scripts to our clients.
Currently all our interface are based within Ms Access (FE), we simply tell our client to download Ms Access runtime for all the users and put the Backend on the server while all the users will have their FE installed on their machine.
Any idea how to deploy this C# interface based application to the clients??????????
0
Cloud Class® Course: C++ 11 Fundamentals
LVL 12
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

I Have linked MS Access table (tblClient_Order)to SQL Server table .
From this table I want to return three fields.

TblClient_Order.Client_Name ---short text
tblClient_Order.Order_Num--- Long Integer
tblClient_Order.Order_Date---- date/time
I need select statement that will pull out Clients with orders which have Order date equal o less than today.
Something as
Dim strSql as string
strSql = “Select co.Client_Name, co.Order_Num, co.Order_Date  from tblClient_Order co
Where DateValue(co.Order_Date) <="  & Date

I tried DateValue(Cstr(co.Order_Date)) <="  & Date this is not working too.
Any idea.
0
I've just put an .accdb on a new computer.  When it opens I get a security warning 'Some active content has been disabled...   Enable Content.  How do I prevent this from happening?

Thanks in advance.
0
I have an interactive access form.   User puts in a number which updates the overall market share.  The market share then searches a table to report back a number based on that market share.  

Everything’s fine except the Search of that table relies on the MS to be updated on the form in a timely manner.  If not, the form reports back invalid use of null because the MS number it’s referencing is blank.

I’ve tried a lot of things in the VBA to get things to pause to give the field time to refresh.  Sometimes I can get it so it works once but the next time it errors out.  

I don’t know what I’m asking except maybe to see if there is a way to ensure the MS calculated field on the form is “done” before it does the search.   I’ve tried every iteration of refresh, requery etc.
0
Hi

In Access VBA is it possible to pull in exchange rates? For instance the $/Euro exchange rate.
0
How do I find the [default] application printer in VBA Access 10 so that I can set the bottom margin.

eg

Dim Ptr as printr
ptr=application.printer  ??
ptr.BottomMargin=0.5

Open in new window


Thanks in advance

Clive
0
This works on an access form.

=Nz(DLookUp("[Rebate]","Rebates",("[foo]=[foo] and ([DMS]) Between [MSH] And [MSL] And ([DDoses]) Between [VH] And [VL]")),0)

But in a complicated scenario I need it to operate to update a table after a value is changed on another part of the form.
I've tried for days and I can't get it to work.  

Rebate, DMS and DDoses are numbers while foo is text

Here, I'm trying to just break it down into components to isolate anything.

Dim db As Database
Set db = CurrentDb

Dim xfoo As String
Dim xDMS As Long
Dim XDDoses As Long
Dim varx As Long

xfoo = Forms!PST.Form![PST1 Subform]![foo]
xDMS = Forms!PST.Form![PST1 Subform]![DMS]
XDDoses = Forms!PST.Form![PST1 Subform]![DDoses]

varx = DLookup("[Rebate]", "[Rebates]", "[foo]= " & xfoo)


DoCmd.RunSQL "Update PST1 Set PST1.Cohort = '" & varx & "'"
0
Hi Expert,

I need help to create a form on attached Access file, when i click a button it should select the "test.dsn" ODBS by default which is saved in C:\Document\test.dsn and  run coding which is on "Query1 _Top100" and that table to exported into Excel on the same path where Access file saved,

I am also ok with VBA for complete coding


Please advise is that possible!

And also please advise is that possible to create MS Access table as ODBS data base for testing purpose to run a query

Thanks!
Database2.accdb
0
Hi all,

I am very new to coding Access 2010 vba so please be patient.

I would like to create a report that will be emailed to multiple recipients but in their own email.  I currently have a qry that has all of the information along with the email recipients name.  I have a table that coincides with the name and has their email address.  I have also created a report that will display all of the information with all of the recipients.

When I click a button, I would like it to look at tbl_Email and grab each record and compare it to qry_FinalReportData and then open up and send rpt_KiosksPerSalesman based on the email address located in tbl_Email.

Hopefully this makes sense, but if not, please let me know and I will try to clarify.

Thanks for all your help.
0
Hello,

I need to create a record from multiple lines of text in csv file. I'm parsing log information. Basically, the record starts at 2018-05-2018 and ends at the next occurance of the  2018-05-2018.   For example:

2018-05-17T11:kfjsadlkfadklgjkaddjfj   dfalksdfsrhtioqeuih     du839457heir5834   odioiqweethen554viir dofjaweweoprweoitrhoiwer
dajrfawdkhboiyib   kdfjag ojern u
2018-05-17T11:32:03.fkdslfjds  dkfj;d ej96nt []

2 records would be created out of the example above:

Record 1 = 2018-05-17T11:kfjsadlkfadklgjkaddjfj   dfalksdfsrhtioqeuih     du839457heir5834   odioiqweethen554viir dofjaweweoprweoitrhoiwer dajrfawdkhboiyib   kdfjag ojern u
Record 2 = 2018-05-17T11:32:03.fkdslfjds  dkfj;d ej96nt []

Currently the file is in notepad.

I have Excel, Access or notepad++

Once I have the multiple lines into 1 record, I can parse out the information I need, but I'm not sure how to combine the lines to make one record.

Any suggestions?

Thank you?
0
Free Tool: IP Lookup
LVL 12
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Hi

I have an Access database used for invoicing customers. A need has arisen to invoice a few customers in other currencies.
How would I store the data in my Invoices table? Should I store local currency in the tables and generate invoice documents in the foreign currency?
I am looking for a simplistic way to achieve this?
Should I use a table to store the exchange rate for each invoice?
Just looking for a few pointers
Thanks
0
Hi Expert's,

I need your help to create a Access Form to run below query into Microsoft SQL server, i am new for MS access, i build query on Excel but client wants that in Access form only,

Could you please help me to create a Access Form with below query , thanks!

And i set 30 seconds wait time to response back from the Server, if not connect then i would like to show some MSG box, is that possible in Access from



SELECT TOP 10000 [PriKey]

      ,[ShipBy]

      ,[Priority]

      ,[Tech]

      ,[ShipMethod]

      ,[InShopDate]

      ,[WONumber]

      ,[Jobs]

      ,[Patient]

      ,[AccountNumber]

      ,[Orthosis]

      ,[Casts]

      ,[Day]

      ,[Comments]

      ,[DesignDate]

      ,[Completed]

      ,[Stage]

      ,[SpecialNotes]

      ,[Bottom]

      ,[DateIn]

      ,[StandardsTime]

  FROM [FabDesign].[dbo].[Schedule]

Open in new window

0
Hi

I’m now stuck with how to get the data from window forms application into the MS access Back end (BE)
The back end has the following tables in Ms Access 2007:
(1)      tblInvoices with the following controls
•      CustomerID (ComboBox)
•      SaleTime (Txt)
•      SalesMan (Txt)
(2)      Sub table (tblSaleslinedetails) linked to the tblinvoice has the following controls:
•      PronductName (ComboBox)
•      WarehouseName(ComboBox)
•      SalesQty (Txt)
•      Price (Txt)
Now the question is, how do I complete the coding in C# form to insert the data in the Back end tables above. Below is the suggested string:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb;
Jet OLEDB:Database Password=MyDbPassword;

We are required to use C# form because of the requirements to interface the POS program which require that our project in C# should have the essential files in the same directory  
Regards

Chris
0
i'm getting run time error 94 , invalid use of null
how to solve this? thx

Condition:
  • 6 unbound textboxes , all set to Null onload.
  • if only 1 textbox retreive value input by user , txtTotal show that value and ignore other null textboxes.(User may input to txt2 or others as first value and leave txt1 as null)
  • if 2 textboxes retreive values, txtTotal shows sum of those 2 values and ignore other null textboxes.
  • if more textboxes retreive value,condition same as above
  • txtTotal is updated when any of the textboxes retreive value.


Codes :
Private Sub txt1_AfterUpdate()
Me.txtTotal = Val(txt1.Value) + Val(txt2.Value) + Val(txt3.Value) + Val(txt4.Value) + Val(txt5.Value)
End Sub

same code goes to Private Sub txt2_AfterUpdate() and so on.
0
In my outlook program I have 2 accounts.  I´m trying to save a appointment in then non default account  but I don´t have any idea how to do it.
Could somebody help  how to do it? thanks.

This is my code buy saved it in the default account

Private Sub AddAppt_Click()
On Error GoTo AddAppt_Err
         ' Primeramente guardar el registro para asegurarnos que los campos requeridos han sido editados.
         DoCmd.RunCommand acCmdSaveRecord
         
         ' Cerrar el procedimiento si la cita ya ha sido agregada previamente a Outlook.
         If Me!AddedToOutlook = True Then
            MsgBox "Esta cita ya ha sido añadida al calendario de Microsoft Outlook"
            Exit Sub
         
         Else
            ' Añadir una nueva cita.
            Const BodyWarning = "Outlook generó esta cita automáticamente basándose en la información recibida de BGB."
            Dim outobj As Outlook.Application
            Dim outappt As Outlook.AppointmentItem
            Dim response As Variant
            Set outobj = CreateObject("outlook.application")
            Set outappt = outobj.CreateItem(olAppointmentItem)
            With outappt
               .start = Me!ApptDate & " " & Me!ApptTime
               .Duration = Me!ApptLength
               .Subject = Me!Appt
               .Body = Nz(Me!ApptNotes, vbNullString) & vbNewLine & vbNewLine & vbNewLine & Space(50) & BodyWarning
               If Not IsNull(Me!ApptLocation) Then .Location = Me!ApptLocation
         

Open in new window

0
I switched the Boolean fields in my database to number fields based on Allen Browne's concerns about the problem with Boolean fields causing errors in queries. I would still like to be able to use checkboxes, so I am sticking with 0 and -1 as my values. However, there are times in list views when I don't want a checkbox because it is too easy to uncheck a box when you are scrolling through. In that instance, I would like to select from  a combobox with the values Yes and No in the list. The table would look like this:

ynID     ynYesNo
0          No
-1         Yes

I know you can create a join on any field, but I have never thought about using negative numbers in a join. My concern is causing a condition where I could corrupt the data, otherwise this seems doable to me. Would this work, or does someone have a better solution?
0

Microsoft Access

219K

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.