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

I've asked this question before and it was solved but wanted to add another criteria to the same question.   I have this sql statement in Access and what I wanted to do is add a field that would read "EARLY" if the number is negative and "LATE" if the number is positive based on  the "DAYS_LATE" field which is a numerical field .  

This was theanswer:
ON-TIME: IIf(DateDiff("d",[CONF_DEL_DATE],[REC_DATE])<=0,"EARLY","LATE")    

My question now is can "EARLY" "LATE" be done against two fields.  Basically REC_DATE first looks at CONF_DEL_DATE and tells me if it's either EARLY or LATE but if it's blank then look at the date of PLN_DEL_DATE to tell me if it's EARLY or LATE.   Can this be done?  See Below SQL Statement......

SELECT DISTINCT tbl_PO_REC.PO, tbl_PO_REC.POS, tbl_PO_REC.SUPP_NO, tbl_PO_REC.SUPP_NAME, tbl_PO_REC.ITEM, tbl_PO_REC.REC_NO, tbl_PO_REC.ORD_QTY, tbl_PO_REC.DEL_QTY, tbl_PO_REC.BACK_ORD_QTY, tbl_PO_REC.TOTAL_COST, tbl_PO_ALL.ORD_DATE, tbl_PO_ALL.PLN_DEL_DATE, tbl_PO_ALL.CONF_DEL_DATE, tbl_PO_REC.REC_DATE, DateDiff("w",[ORD_DATE],[REC_DATE]) AS LEAD_TIME_IN_WKS, DateDiff("d",[PLN_DEL_DATE],[REC_DATE]) AS DAYS_LATE, IIf(DateDiff("d",[CONF_DEL_DATE],[REC_DATE])<=0,"EARLY","LATE") AS [ON-TIME]
FROM tbl_PO_REC LEFT JOIN tbl_PO_ALL ON (tbl_PO_REC.POS = tbl_PO_ALL.POS) AND (tbl_PO_REC.PO = tbl_PO_ALL.PO)
WHERE (((tbl_PO_REC.SUPP_NO)<>59944 And (tbl_PO_REC.SUPP_NO)<>16106 And (tbl_PO_REC.SUPP_NO)<>25278 And (tbl_PO_REC.SUPP_NO)<>27001 And …
0
Is it possible to apply conditional formatting to a complete row containing several fields, if I just set the rule in the first field?

E.g. - I have applied the rule to the company name so each company has a specific colour, but I would like all the fields in that row to have the same colour

Company     Description      Client     Job Number   Etc
Company     Description      Client     Job Number   Etc
Company     Description      Client     Job Number   Etc
Company     Description      Client     Job Number   Etc
0
Trying to put in a field that records the windows username as a default when entering new information.
I have such a module using a public global function - however when I type in the name MyUserName() it won't allow me to use that or even save it.

What syntax do I need for this to be accepted?
Please note sample error message and sample Access database:

Public Function MyUserName() As String
On Error GoTo err_proc

    Dim sUser As String
    sUser = Environ("UserName")
    MyUserName = sUser
    
Exit_Proc:
    Exit Function
    
err_proc:
    Resume Exit_Proc


End Function

Open in new window

SaveWinUserName-as-defaultPNG.PNG
myusernamewontwork.accdb
0
I've got some perfect .gifs in a web browser control in Microsoft Access side by side on some entry screens.  It looks really cool.
However I'm not sure how to shrink the image as is shown on other webpages a little smaller than what it currently looks like.

Does anyone know a touch of html and where to put it for this web browser control in Microsoft Access using VBA?

Here's a sample database I put together:
The gif is taken from:

http://i.imgur.com/3h6gipf.gif

Form1 is the size I want the .gif to be
Form2 is the only way I know how to display it all
I want to reduce the size though!
EEdbsample.accdb
0
I'm trying to use this in a query but am not getting the correct result.  Probably because of the syntax.

Expr1: DCount("[PracticeVisitRecordID]","tblPracticeVisits","[VisitDate] Between Forms![frmSelectRecord]![txtStartDate] And [Forms]![frmSelectRecord].[txtEndDate]") And [Zone]=[Forms]![frmSelectRecord]![txtZone]

Can someone help?
0
I have a locations and Sales table.

LocationID  Location Name
1,  Philadelphia
2, Boston
3, Miami
4, Denver


I need VBA that will use the following query Select * From Sales where S_LocationID=? and loop through all the locations and save a separate excel file with the sales from each location
0
I have a main form (form 1) with a combo box that shows students. on the update of the combo I rin the code to change the sql of a query which is part of a subform.

The query amends correctly but the rows on the subform do not refresh or requery unless I close and open the form.

Private Sub CBStudentRL_AfterUpdate()
Dim strQuery As String
Dim db As DAO.Database
Set db = CurrentDb
Dim StudentIDIN As Long
Dim RstBalance As DAO.Recordset
Dim StrOutput As String
Dim qd As DAO.QueryDef


StudentIDIN = CLng(Me.CBStudentRL.Value)

strQuery = "SELECT qAllBooksStudents.Student_id, qAllBooksStudents.ReadID, qAllBooksStudents.Level, qAllBooksStudents.Title, qAllBooksStudents.[Word count]," _
& " qAllBooksStudents.[Module Level] AS [Read level], QryStuudentMaxLevel.MaxOfModuleLevel AS [Current student level]," _
& " [QryStuudentMaxLevel].[MaxOfModuleLevel]+1 AS [Level Plus one] " _
& " FROM (qAllBooksStudents LEFT JOIN Tblreadlistlink ON (qAllBooksStudents.Student_id = Tblreadlistlink.Student_id_fk) AND " _
& "(qAllBooksStudents.ReadID = Tblreadlistlink.book_ID_fk)) LEFT JOIN QryStuudentMaxLevel ON qAllBooksStudents.Student_id = QryStuudentMaxLevel.Student_ID" _
& " GROUP BY qAllBooksStudents.Student_id, qAllBooksStudents.ReadID, qAllBooksStudents.Level, qAllBooksStudents.Title, qAllBooksStudents.[Word count]," _
& " qAllBooksStudents.[Module Level], QryStuudentMaxLevel.MaxOfModuleLevel, [QryStuudentMaxLevel].[MaxOfModuleLevel]+1, Tblreadlistlink.Student_id_fk," _
& " 

Open in new window

0
Hi,

From within MS Access VBA I was going to use the below code I found on the Web to loop thru a folder and load each Excel file (the example has text files) into a DB called Raw.  My only challenge is I do not want to import every column.  I just want to import column 1, 5, 6 , and 15.  The below example loads all columns.  How would I achieve during the import only loading the desired columns.

Option Compare Database

Private Sub Command0_Click()


        Dim strPathFile As String, strFile As String, strPath As String
        Dim strTable As String
        Dim blnHasFieldNames As Boolean

        ' Change this next line to True if the first row in EXCEL worksheet
        ' has field names
        blnHasFieldNames = True

        ' Replace C:\Documents\ with the real path to the folder that
        ' contains the EXCEL files
        strPath = "C:\Users\rschuell\Desktop\test\"

        ' Replace tablename with the real name of the table into which
        ' the data are to be imported
        strTable = "tablename"

        strFile = Dir(strPath & "*.txt")
        Do While Len(strFile) > 0
              strPathFile = strPath & strFile

              DoCmd.TransferText _
                TransferType:=acImportDelim, _
                TableName:=strTable, _
                FileName:=strPathFile, _
                HasFieldNames:=blnHasFieldNames


        ' Uncomment out the next code step if you want to delete the
        ' EXCEL file after it's been imported
     

Open in new window

0
Need to know what you guys have used for scenarios where a combo box not in list scenario?
Ya'll the Microsoft Access VBA experts- what have you used when there was something in a combo box and they wanted to add a new item - how did you handle it?

Any samples you can provide as to a best method?
0
Hi.
I using Microsoft Access 365.  I need to export / create a clean csv file.
My table only has 3 fields.

[System ID]          Data type  =  Number         Field Size = Double             Decimal places = 0
[Order Qty]          Data Type = Number          Field Size = Integer             Decimal places  = 0
[Unit Cost] is        Data Type  = Currency        Format = General               decimal places = 2

This is my export command.
 DoCmd.TransferText acExportDelim, , str_tbl_ToExport, strTheDirectory, True, , 65001

Open in new window


I am trying to export a from a table to a CSV file.  Below is the result
 *** Please note that I inserted spaces in this  question to make it easier to read. ***
This is what I get.    

"System ID",                  "Unit Cost",  "Order Qty"
210000000347.00,         $4.20,                    1

See below for what I need.

System ID,              Order Qty,             Unit Cost
210000000347,           4.2,                      1

Any help would be greatly appreciated.
0
I was just importing all front end objects into a new Blank DB and got this error. Anyone seen this before? All files are on a local pc with no association with any server.  .Ist error

Then after clicking OK I got this2nd error
Then I got this3rd error
Then this 4th error
0
I have an ADODB.Recordset in Access VBA that I need flushed out to a table in Access.  

Dim RowSet as ADODB.Recordset
comm.CommandText = "[qryA042 new claims payors - Need Groups]"
comm.CommandType = adCmdTable
Set RowSet = comm.Execute

Open in new window


The data is coming from a SQL Server view hence why using ADODB.  So how do I get that into a table in the current Access database?

James
0
I am connecting one computer directly to a server to open MSAccess.exe.    I am getting the attached warning.  

How do I change the security settings to stop the warning?

accesswarning.png
0
I need the best Microsoft Access VBA scripting I can find that will create an email and send to a person without the pop-up shown in the following attachments.
From my experience, it's been using the CDO coding but I can't seem to find what the ip address of the current company's smtp server or not sure if they use port 25.

This is the one I want to use but don't know the smtp path:   Thus when I try I get a transport failed to connect to server - 21472209373 (80040213) - (see attachment:  Email Not Working A)  which I think means they would have to make a setting work on the email server to allow this through.  This error message appears and highlights yellow - line where it states objMessage.Send

So from all of your experience - how have you got this to work?  Or is there any other coding I'm missing that's better than below?

Public Sub SendEmailwoOutlook(ByVal sTo As String, ByVal sSubject As String, ByVal sTextbody As String, Optional ByVal sFrom As String = "", Optional ByVal sHTML As String = "")
On Error GoTo err_proc

  Dim objMessage As Object
  Set objMessage = CreateObject("CDO.Message")
  'sCC is the copy
  Dim sCC As String
  If sFrom = "" Then sFrom = STARTUP_DBADMINEMAIL
  If sTo = "" Then GoTo Exit_Proc

  objMessage.Subject = sSubject
  objMessage.FROM = sFrom
  objMessage.To = sTo
  objMessage.CC = sCC
  objMessage.TextBody = sTextbody
  If sHTML <> "" Then objMessage.HTMLBody = sHTML

  '==This section provides the configuration information 

Open in new window

0
Hi

Kindly help me on this one again I’m hitting a block, I was thinking that it was so easy to do, I want to reference a control within a sub form in Ms Access:
(1)      sfrmLineDetails Subform
(2)      Target control which is data source is LineTotal
(3)      The required control to be update is called txtSTDCost which bound to the tblLinedetails table
I have tried the code below but it’s not updating:
Private Sub LineTotal _AfterUpdate()
Me! txtSTDCost = Me!LineTotal
End Sub

sales-accounting-final.png
I hope you can the 1700  that is the one I want to be update to a bound control

Regards

Chris
0
I am trying to build a reservation system in Access (see attached image) but I am struggling with this excel type of interface. Is there any way to build something similar in Access?? I would very much appreciate any thoughts or suggestions.
ScreenShot.jpg
0
Hello experts,

With VBA I need to convert an MS Access report to a password protected PDF (you can't read it without the password) and then send it with email.

I know this can't be done without a 3rd party application - I am aiming to use PDFCreator.

The following code (in a Class Module) shows the current method I'm using for sending reports but without password:

Private Sub sendEmail_Click()  ' Send report with email
    If IsNull(Me![language_ref]) Or IsNull(Me![interpreter_ref]) _
    Or IsNull(Me![type_of_job]) Then
        Beep
        MsgBox "Language, interpreter and assignment me be filled out"
    Else
        Dim strWhere As String
        
        If Me.Dirty Then    'Save any edits.
            Me.Dirty = False
        End If
        
        strWhere = "[booking_ID] = " & Me.[booking_id]
        DoCmd.OpenReport "rptBókanir2", acViewPreview, , strWhere
        
        email = DLookup("email_address", "interpreters", "interpreter_id = " & Me![interpreter_booking])
        emailSubject = "An interpretation order"
        DoCmd.SendObject acSendReport, "rptBókanir2", acFormatPDF, email, , , emailSubject, _
        "Please read the attached PDF", True
        
        DoCmd.Close acReport, "rptBókanir2"
        
    End If

End Sub

Open in new window


I might note the password will be static. The Access is version is part of Office 365.

Please can someone help me with this as I am completely rusty in all Access and VBA related matters!
0
I have a form that has a listbox.  (Simple Multi-Select)  When the user clicks on an item in the list a record gets written to a table the form is bound to.  But when the user closes the form and comes back to it I would like the selected items to be "selected" again.   In other words, show the user which items were previously selected.  Can this be done?


Also then either during the initial selection or when the user re-opens the form and if they UNselect the item, I'd like the record to be erased from the table.  Can this be done?
0
What is wrong with this code:

Set RS = CurrentDb.OpenRecordset("Select * from tblClientProjectTaskList where [ClientProjectTasksID] = " & Me.txtProjectTaskID & ")

ProjectTaskID is a number field.

Access VBA doesn't seem to like it.
0
Hi,

I have a report with the following information:

- total pairs, the control is =[RainPO].[column](17) and shows 120

- total pairs per ticket, the control is RainPairPerTicket and shows 40

- print ticket, this hidden control indicates the number of tickets that will be printed, the control is =[RainPO].[column](17)/[RainPairPerTicket] and shows 3

- total pairs appearing on each ticket, the control is =[RainPO].[column](17)/[PrintTickets]

So far, all of this works, the thing I don't know what to do is for the report to print 3 times or if the number is not divisible by 40, for example, is the total pairs is 100, I would need for the report to print twice with 40 and once with 20

Can this be done and if so, how?

Thank you for your help

Jeannie
0
Access:  I need to pull a report for which PMs are due.
I have a start date
I have an interval (in hours)
I need to add the interval to the start date (got that) to identify the first call date.
Then as the call date passes I need to add the interval to the last call date on a continually basis.
0
Access:  I need to add a "Interval" to a date, in a query.   I have a field labeled "Start_Date" / Date/Time   and a field labeled "Interval"/Number
The field interval is in hours.

I need to add the intervals to the start date for a NEW start date.
0
Hello Team

Can you please advise how to hide specific values from a combobox list?

For example:  I have a main form which is used to either display previously created records or can be used to create new records.  When the form is loaded to display a history record, the combobox cboErrorCat displays, Error1, Error2, Error3, Error4 when selected. What I require is for the cboErrorCat to display only Error1 and Error2 when the form is loaded to create a new record.

Main Form is called frmPaymentError, combobox is named, cboErrorCat and the row source for the cboErrorCat combobox is called qryErrorCategories

Query SQL
SELECT tblErrorCategories.ErrorCategoryID AS Expr1, tblErrorCategories.ErrorCategory AS Expr2
FROM tblErrorCategories
WHERE (((tblErrorCategories.DateExpired)>Date()))
ORDER BY tblErrorCategories.Order;

Open in new window



Any assistance would be much appreciated.

Thanks in advance.
0
We have an Access database on a Win 2012 R2 server locally.  The backend and frontend are separate and each user has there own frontend.  

When they use it from the office where it is stored the performance is very poor.

When they access it via Citrix, it is extremely fast.  

This makes no sense to me.  Why is it faster over an MPLS connection?

How can I improve local performance?
0
How to force the same value in another combo box from another combo box?

All please note the following sample file.
Basically after someone selects a value in ComboA - it supposed to make ComboB - appear with the same value in another table.

How do I do this with VBA code in the AfterUpdate event?

Again, I provided a sample so it would be easy to try.  Please note the two tables have similar values but different field names
Thanks in advance..

Private Sub cboA_AfterUpdate()
  
  Dim sCompany As String
  sCompany = cboA.Column(1)
  'MsgBox (cboA.Column(1))
  
End Sub

Open in new window

Database1.accdb
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.