UtterAccess.com Access Forums

If Anyone Can Resolve This For Me I Will Not Come On Here Again.

Wed, 16 May 2012 19:17:51 -0400

Hi Could somone please figure this out scratching my head with it now

On the Match Date form when I select Home for match type and then enter player name score then opp player Name and score it should auto populate with 2 points if its an away home win and 3 points if it is an away win per player.

If my player points at home =21 then my player will receive 2 points and if my player away score is 21 then my player received 3 points.

It all works up until i delete the test data to start entering actual data, could someone take a look and try and figure it out please.

This works for a bit but when you click on add new match details the points do not auto calculate.

Graphing Issues

Wed, 16 May 2012 18:24:22 -0400

My client wants a report with a chart that requires a secondary axis. I can produce such a chart but, quite frankly, it looks awful. Most values on the primary axis are positive but there is one that is a rather large negative. The primary axis is plotted as a column chart. The main problem with this part of the chart is that the axis label falls inside the chart itself. Playing with the offset I am not able to get it to move more than half way to the bottom of the plot area.

The secondary plot is also terrible. I have played with colour and weight for the line but cannot get a line joining all the dots. Are there any charting alternatives that will give a more pleasing result?

Glenn

Combo Box Does Not Display All Columns

Wed, 16 May 2012 17:52:31 -0400

Hello and thank you in advance for your consideration!

I have many forms with combo boxes. Almost all the combo boxes have multiple fields or "columns" like in the attached image 'Combo box w fields.jpg'.
However, when you're not in that field, only the first column is displayed; as in the other attached image 'Combo box one field displayed.jpg'.
I would like my users and myself to be able to view all the columns; as in my examples- - Model Number, Rev. and Description. But only the model number is displayed.
I have attached my properties too, just in case.

Thanx,
Gary

Run Report For All Users

Wed, 16 May 2012 17:47:34 -0400

I have a report that pulls information from a query. Specifically it brings back a It is run from a form with a dropdown list containing staff member IDs (Id 1,2,3...).

From the form fmReports they click on the report, then they select a staff member from a combobox (which is filled by a lookup table), then they hit "Go"

'Simple example of query now:
Select * from tblReports Where StaffID = [Forms]![fmReports]![cboStaff]
'When filled in it looks more like the query below
Select * from tblReports Where StaffID = 1

I'm wondering if there is a simple way to run that same report for ALL Staff IDs. Right now we can assume we have 7 staff member with datatype Long and IDs of 1 to 7.

Select * from tblReports Where StaffID is Between 0 and 90 (grouping on Staff ID, sorting on a date field)

Jim

Can Instr(string) Be Used As The Update Criteria For A Field?

Wed, 16 May 2012 17:09:44 -0400

Hi,

I was hoping someone could help me "see the light" with the issue I'm having. I've provided the vba code below. The vba code is looping through a multilist box. There are two text boxes hidden on the form. TextBox1 is a string of item numbers, TextBox2 is a string of location codes that correspond to the item numbers in TextBox1.

Example:

TextBox1: 095716, 167017, 180886, 180887
TextBox2: A404A,A404A,A103A,A102A

Item # 095716 needs to be updated to location A404A .... Item # 180887 needs to be updated to location A102A.

I've been updating the location code by having a user select a location from a combo box and then all items for the location were being displayed in the multilist box. Then based on which items the user selected I used

CODE

InStr([Forms]![ReceivingPropertyTransferForm]![TextBox1],[ItemNumber])

as an expression in my update query to update the location code field selected in the combo box for each item number that appears in TextBox1.

I had to change that design and now display the location code as column 2 in the multiselect box. I want the user to select the row in the multiselect box and then have it update for each item number (column 0) the location code based on column 2 in the multiselect box. I have the two strings (TextBox1, TextBox2), but I don't know how to use the TextBox2 string as update criteria and match it to the item number in TextBox1.

CODE

If IsNull(Me.TB_Email.Value) Then
MsgBox "Unable to complete Property Transfer(s) approval." & vbNewLine & "You must enter your work e-mail address in order to view pending transfer(s)."
Exit Sub

Else

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Dim strSQL2 As String
Dim lngLen As Long
Dim lngLen2 As Long

    Set frm = Forms!ReceivingPropertyTransferForm!
    Set ctl = frm!LB_MutliSelectItem

    For Each varItem In LB_MutliSelectItem.ItemsSelected
       strSQL = strSQL & Me!LB_MutliSelectItem.ItemData(varItem) & ","
       strSQL2 = strSQL2 & Me!LB_MutliSelectItem.Column(2, varItem) & ","
    Next varItem
    lngLen = Len(strSQL) - 1
    lngLen2 = Len(strSQL2) - 1
TextBox1 = Left$(strSQL, lngLen)
TextBox2 = Left$(strSQL2, lngLen2)

File Identifier Is Stuck

Wed, 16 May 2012 16:07:48 -0400

Below is my code. Basically I want to read a series of CSV files and import them into the current workbook, adding them to the end of the existing data. Today is 5/16, so I want to find the file for 5/15 and copy its data. The MsgBox statement gives me the correct file name and stops the processing and I can see that the correct file is open. I clikc OK and processing continues. However, the program manages to copy the data from the same file with a date stamp of 5/10. It doesn't matter what the current date is. I open the correct file for the date I want to copy, but the program always copies the data for the file for the 10th.

I even renamed the file for the 10th to a name that doesn't match the pattern, so I know I can't be opening it accidentally.

This code used to work until the 10th. Then it lached onto that file and won't let go.

CODE

Option Explicit

Sub ImportNTFDaily()
Dim RptPath As String, RptName As String
Dim nReports As Long, kReports As Long
Dim StartDate As Date, EndDate As Date
Dim xlBook As Excel.Workbook, xlReport As Excel.Workbook
Dim rCopy As String

Set xlBook = ThisWorkbook
RptPath = Trim(Range("ReportPath"))
StartDate = Range("NTFStart")
EndDate = Range("NTFEnd")

' Clear out old data
Sheets("Control Panel").Range("I3:I13").ClearContents
If Range("NTFNextRow") > 2 Then
    Sheets("NTF Daily").Range("A2:A" & LastRow("A", "NTF Daily")).EntireRow.Delete
End If

Application.DisplayAlerts = False
For kReports = 1 To EndDate - StartDate
    RptName = "CLNR NTF by POS RET CODE " & Format(StartDate + kReports, "yyyy-mm-dd") & ".xlsx"
    If Dir(RptPath & RptName) = "" Then
        xlBook.Sheets("Control Panel").Cells(kReports + 2, 9) = RptName & " - not found"
    Else     ' If the workbook exists, open it
        Set xlReport = Workbooks.Open(RptPath & RptName)
        rCopy = xlReport.Sheets(1).Range("K1").End(xlDown).Address
        MsgBox "Report name is:" & xlReport.Name
        xlReport.Sheets(1).Range("A2:" & rCopy).Copy _
            xlBook.Sheets("NTF Daily").Range("A" & xlBook.Sheets("NTF Daily").Range("$N$1"))
        xlReport.Close savechanges:=False
        xlBook.Sheets("Control Panel").Cells(kReports + 2, 9) = RptName & " - processed"
    End If
Next
Application.DisplayAlerts = True

End Sub

Query Speed

Wed, 16 May 2012 15:48:25 -0400

First, sorry about the cross post but realized I put this question in the wrong forum.

I have this sql statement, that with the help of others, that produces the results that I am looking for. It displays all required fields and and then calculates the difference in miles from one row to next (displaying the number as either a + or - number. Without a criteria it will produce a result in about 4 seconds (1400 records). If I add the criteria of >0.0001 on the calculated column the query takes about .04 seconds per record, 1 minute 8 seconds for 1400 records or aboutt 5 minutes to run against 7000 records (almost a lifetime).

I have tried making this an update query (without the criteria) with the hope of running a second query,with criteria, against that table, but even without the criteria the update query still took a minute 4. Any thoughts on how this can be made to run faster?

Ussing Access 2010

SELECT T1.Date, T1.Route, T1.BookingID, T1.StreetNumber, T1.Street, T1.Arrive, T1.Perform, T1.Miles, T1.Miles-(SELECT Miles FROM TblPrimaryData As T2

WHERE T2.Route = T1.Route AND T2.IDNumber = (SELECT MIN(IDNumber) FROM TblPrimaryData AS T3
WHERE T3.Route = T1.Route AND T3.IDNumber > T1.IDNumber)) AS Difference FROM tblPrimaryData AS T1

GROUP BY T1.Date, T1.Route, T1.BookingID, T1.StreetNumber, T1.Street, T1.Arrive, T1.Perform, T1.Miles, T1.IdNumber, T1.Status, T1.Activity

HAVING (((T1.Miles-(SELECT Miles FROM TblPrimaryData As T2 WHERE T2.Route = T1.Route AND T2.IDNumber = (SELECT MIN(IDNumber) FROM TblPrimaryData AS T3 WHERE T3.Route = T1.Route AND T3.IDNumber > T1.IDNumber)))>0.001))

ORDER BY T1.IdNumber;

Update Table Using Multiple Recordsets? (maybe?)

Wed, 16 May 2012 15:34:54 -0400

Here's what I'm trying to do and I apologize if I'm headed the wrong direction. I'm trying to cycle through the records in table t_Directory (about 6 to 10 records) and for each .xlsx file, open the Excel file and update a second table called t_SheetInfo with the FileID of the Excel Worksheet and worksheet count and the worksheet name. I thought I just about had it, but I'm getting an error on

CODE

rs2.Fields("[SheetName]") = WorkSheetName(WorkSheetCount)

line. Would anyone mind steering me in the write direction? Thank you in advance for any help!!

CODE

Dim db As DAO.Database
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet

Private Sub CycleThroughWorkSheets()
    Dim rs As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim sSQL1 As String
    Dim sSQL2 As String
    Dim sSQL3 As String
    Dim rsFilePath As String
    Dim rsWSName As String

    sSQL1 = "SELECT t_Directory.FileID, t_Directory.FilePath FROM t_Directory " & _
            "WHERE (((t_Directory.FileExtension)=""'xlsx'""))"
    Set rs = db.OpenRecordset("sSQL1", dbOpenDynaset)
        With rs
            rs.MoveFirst
                Do While Not rs.EOF
                    rsFilePath = rs.Fields("[FilePath]")
                    OpenWorkBook (rsFilePath)
    Set rs2 = db.OpenRecordset("t_SheetInfo", dbOpenDynaset)
        With rs2
            rs2.MoveFirst
                Do While Not rs2.EOF
                    rs2.AddNew
                    rs2.Fields("FileID") = rs.Fields(1)
                    rs2.Fields("[SheetIndex]") = WorkSheetCount(rsFilePath)
                    rs2.Fields("[SheetName]") = WorkSheetName(WorkSheetCount)
                    rs2.Update
                Next
                Loop
        End With
        End With
    Set rs = Nothing
    Set rs2 = Nothing
End Sub

Public Function WorkSheetCount(rsFilePath As String) As Integer
    Set xlWB = xlApp.Workbooks.Open(rsFilePath)
          WorkSheetCount = xlWB.Sheets.Count(rsFilePath)
          Debug.Print "WorkSheetCount : " & WorkSheetCount
End Function

Public Function WorkSheetName(WorkSheetCount As Integer) As String
    Set xlWB = xlApp.Workbooks.Open(rsFilePath)
          WorkSheetName = Worksheets(WorkSheetCount).Name
          Debug.Print "WorkSheetName : " & WorkSheetName
End Function

Email Issue In Access 2007

Wed, 16 May 2012 15:26:53 -0400

I am attempting to send an email using the following lines of code -- both fail:

Private Sub Export_Click()
On Error GoTo Err_Export_Click

' DoCmd.SendObject acReport, "rptProject", acFormatPDF, Email_TO, , , "Sample Email Subject", , False
DoCmd.SendObject acReport, "rptProject", acFormatPDF, "RealEmailAddress@yahoo.com", , , "Sample Email Subject", , False

Exit_Export_Click:
Exit Sub

Err_Export_Click:
MsgBox Err.Description
Resume Exit_Export_Click

End Sub

My email uses Outlook Web Access and I am running over a wireless connection. When the code runs I get prompted for my email password. Sometimes I get prompted over and over then the email appears to have been sent but wasn't. Sometimes I am not prompted. Problem is that email is NEVER actually sent. Anyone have any suggestions?

The prompts are attached.

Text Box Formatting Dependent On Other Field Value

Wed, 16 May 2012 14:56:26 -0400

Hey everyone,

I have a report that shows various Accounts (Field name is "Account Name") as well as their corresponding values (Field name is "Q1"). Within the Q1 field, there are numbers (Standard format needed) and as percentage (% format needed). Is there any way for the format of Q1 to be dependent on the value in "Account Name" ?

For example, if Account Name = "Margin", then format Q1 as %, else format it as Standard. All of the values are calculated outside of Access, therefore they are all contained within one field (Q1).

I need the report to look like this:

CODE

Account Name                                         Q1
Sales                                               1,000
Costs                                                500
Profit                                               500
Margin                                               50%

Thank you for any and all help!

Cannot Filter A Form Using Two Criteria

Wed, 16 May 2012 14:54:09 -0400

Access 2010. Windows 7

If I filter a form using just one criteria ( StLinkCriteria = "[Picture_Name]=" & "'" & Me![Picture_Name] & "'"),
it worked. If however, I filter a form using two criteria, I get an error message
"Run-time error 3075. Syntax error missing operator in query expression.

////////////////////////////////////////////////////////////////////////

Private Sub Picture_Name_And_Date_AfterUpdate()
Dim StLinkCriteria As String
Dim StLinkCriteria_2 As String
Dim Date_And_Time_Str As String

Date_And_Time_Str = Picture_Name_And_Date.Column(1, Picture_Name_And_Date.ListIndex + 1)

StLinkCriteria = "[Picture_Name]=" & "'" & Me![Picture_Name] & "'"
StLinkCriteria_2 = "[Picture_Name_And_Date]=" & "'" & Date_And_Time_Str & "'"

StLinkCriteria = StLinkCriteria & StLinkCriteria_2
DoCmd.OpenForm "FRM_TAGNAME_USED", , , StLinkCriteria
End Sub

Address Subform

Wed, 16 May 2012 14:48:23 -0400

Hi

I need help with my database.

Im uploading my database and if you open frmCompanyDetails you will see what Im trying to do, I had some help through this forum and also privately from a member of UtterAccess. The problem is I am teaching myself access as I go along and it is difficult - but Im not going to let that stop me.

The main form of my database seems perfect at this stage and so is the subform for contacts - ideas for these areas of my database will be welcome.

But now I come to the ADDRESSES part of my form (its also a subform) and thats where I get stuck.
For any of my companies I will have at least 3 addresses. A physical address, a postal address and a delivery address.
Natrually the physical address will be a street address and the postal address and delivery address could be either a street address or a P.O. Box address.
A customer may also make a request that a certain address be delivered to - this needs to be kept on record since I may need to deliver to that address
There also maybe another physical addressagain.
I was thinking that a field (to be later placed on a form) needs to be created for every part of the address (i.e. The 4 lines of a usual address and only those relevant displayed. Difficult to describe since I want to separate the lines of each address and dont particularly want to display "empty" columns. The unheard of may happen - what if a certain address is 5 or 6 lines long, what do I do?
Examples of address (Physical)
123 Saxon Road, Heights, 7300 - 3 lines
1542 Plain Street, Bromley Manor, Johannesburg, 2456 - 4 lines
Office 2A, 15 Walker Avenue, Meadowfields. Apple Valley, 1246 - 5 lines

Example of postal address
P.O. Box 175, Cape Town, 8000

There also maybe another physical address - perhaps the company has another branch, also there maybe another postal address because the company may have another mailing address.
Now a delivery address and a postal address can be the same as the physical address, things could be delivered to a P.O. Box and a special delivery address could be specified which could be a physical address or a postal address.

I have been trying to rack my brain on how to do this in access but I cannot.

Modifying A Report Filter

Wed, 16 May 2012 14:34:45 -0400

Think I posted this in the wrong forum initially, so forgive me for double-posting. I inherited an Access report that has the following filter (found this on the Property Sheet):

[Dept]="1942"

I need to modify it to include another dept (1941). I have tried several ways but none work:

[Dept]="1942","1941"
[Dept] IN "1942","1941"
[Dept] In "1942","1941"
[Dept] In List "1942","1941"
[Dept] In List ("1942","1941")

I have very limited experience with Access, and I'd greatly appreciate it if anyone could tell me the correct syntax to pull both depts. If it matters, the Record Source is a query, but the query itself does not have any selection criteria regarding depts.

Many thanks,
Steve

Sql Query In Vba Against Rangename

Wed, 16 May 2012 14:19:02 -0400

i'm ok to write a sql query in vba against an outside source (see below), but i would like select from a range (name) within my current workbook...?

Example:
Dim cn As ADODB.Connection, rs As ADODB.Recordset, f As Integer, r As Long
strSourceFile = "\\03-server\data\database\Holding_Schedule_RG.xls"
strsql = "SELECT * FROM [Jobs$];"
'connection...
Set cn = New ADODB.Connection
On Error Resume Next
'open connection...
cn.Open "DRIVER={Microsoft Excel Driver (*.xls)};DriverId=790;ReadOnly=True;" & _
"DBQ=" & strSourceFile & ";"
'recordset
Set rs = New ADODB.Recordset
'open rs
rs.Open strsql, cn, adOpenForwardOnly, adLockReadOnly, adCmdText

???

thank you.

Connection Not Being Closed Gridview

Wed, 16 May 2012 14:03:46 -0400

I have a web site created through VS 2005 (ASP.NET and VB.NET)

I add a gridview control and an access data source control and allow it to display some data.

All I am doing is giving the access data source a DB, and then passing a query from that to the gridview control. When I do this it creates an LDB file. This file stays open until I completely kill the connection or completely close VS.

I have tried to manually close the connection, I have tried to dispose it. I have tried putting everything within Using / End Using and everytime the connection is not closed.

Any thoughts as to why the connection does not close, or some way to close the connection?

I have tried google and have come up blank so far.

Multiple Selection

Wed, 16 May 2012 13:44:47 -0400

do i have options for the user to select multiple choices other than a listbox...?

???

thank you.

Insert Records Avoiding Duplicates (not Primary Key)

Wed, 16 May 2012 13:00:15 -0400

Hello, I am kind of stuck with what I thought it was a simple query:

I have 2 tables (fields of each table):

LEADCAMPANIA (ID_LEADCAMPANIA, ID_LEAD, ID_CAMPANIA)
FILTRO_LEAD_AUX (ID_LEAD)

I need to insert all records from FILTRO_LEAD_AUX into LEADCAMPANIA. The ID_CAMPANIA value is stored in a variable called "intIdCampania". So the INSERT query I use is this:

INSERT INTO LEADCAMPANIA (ID_LEAD, ID_CAMPANIA) SELECT FILTRO_LEAD_AUX.ID_LEAD, " & intIdCampania & " AS Expr1 FROM FILTRO_LEAD_AUX

And it works fine

The problems start when I do not want duplicates to be inserted into LEADCAMPANIA. I need duplicate ID_LEAD records being excluded from the query, but only those where ID_CAMPANIA = intIdCampania. E.g:

FILTRO_LEAD_AUX
1
2
3

LEADCAMPANIA
A|1|100
B|2|200

If the value of intIdCampania is 200, then records 1 and 3 should be inserted into LEADCAMPANIA but record #2 should not because it is already in LEADCAMPANIA and its ID_CAMPANIA matches the current intIdCampania value

How can I do this through a SQL statement? should I add a Where condition at the end of my existing query?

Hope I have explained myself properly

Thanks!

Argh! Ms Access Stopped Working When Trying To Link To A Lan Mysql Server!

Wed, 16 May 2012 12:45:05 -0400

so why is this happening? my thing is weird. for some reason, when i link the tables on the mysql server ms access stops working! any ideas?

Merge 2 Tables

Wed, 16 May 2012 12:35:24 -0400

I am planning my Access DB.
We want to create a product table with unique id, from the ERP - But as the ERP deleted history once sales are invoiced, we want to maintain the history in Access. Hence we would regularly download the product usage data from the ERP into a new table

Or can we import to one table, would that table overwrite and loose the data.

So the plan is to download into a new table, but then I want to merge the new and old tables and preserve the historic data. But how do I merge the two sets of data tables -all fields will be the same in each. I am using Access 2010.

thanks
Colin

Instr Function

Wed, 16 May 2012 12:20:58 -0400

when the form is closed the Notes field has the Date appended to the front of it with #'s leading and ending. I want to be sure that it does not
get entered more than once so if the # is already in the Notes field it should just add to the already existing notes entry without adding in the date and the leading and ending #'s

dim strfinddate as integer

strfinddate = InStr("notes", "#")
If strfinddate < 1 Then
notes = "#" & Date & "#" + " " + notes
Else
notes = notes + notes
End If