|
 |
| Recent blog posts: | |
|
| They have birthday today: | |
|
| Forums: | | |
|
| Discuss: | |
|
| Recent forum topics: | |
|
| Recent forum comments: | |
|
| Модератор: | |
|
Saturday, 12 August 2006
|
| selecting where count > 1 Ericv 00:19:48 |
| | I have 3500 records in a table - each record has a unique value (KEY_ID
field), but some records share the same value (in a field called
POLE_ID) So, there may be 3 records that have the POLE_ID of 25, 4
records with the POLE_ID of 31, etc. I would like to grab all the
records in the table that share POLE_ID's - that is, select out the
records where there is more than 1 of the same POLE_ID value.
I then want to populate a form using this query.
Is this possible and how?
Thanks
Eric V
Gurnee, IL
|
| |  2 answer | Add comment |
Friday, 11 August 2006
|
| My Code: Invalid Procedure call or argument Eric 23:29:17 |
| | When I run my script it gives error on the following line:
strEmail = Right(strEmail, (Len(strEmail) - 1))
I enclose my code and the sample text file too
Thanks,
-----------------------------------------------------------------------------
Option Compare Database
Option Explicit
Private Sub Command26_Click()
DoCmd.Close
End Sub
Private Sub cmdParse_Click()
DoCmd.SetWarnings False
Dim strfile As String
Dim AcctNum As String
AcctNum = "Account Number"
'Box
Credits----------------------------------------------------------------------------
'-----------------------------------------------------------------------------------
ChDir ("C:\MailSave\Requests\")
strfile = Dir("C:\MailSave\Requests\200" & "*.*")
Do While Len(strfile) > 0
FileCopy "C:\MailSave\Requests\" & strfile,
"C:\MailSave\GetInfo.txt"
Dim fileName As String
Dim stemp, linesfromfile, nextline As String
Dim iFIle As Integer
iFIle = FreeFile
Open "C:\MailSave\Requests\" & strfile For Input As iFIle
While Not EOF(1)
Line Input #1, nextline
linesfromfile = linesfromfile + nextline + Chr(13) + Chr(10)
Wend
Close iFIle
Call TestReplace
Me.txtEmail.Value = linesfromfile
Kill "C:\MailSave\Requests\" & strfile
strfile = Dir("C:\MailSave\Requests\200" & "*.*")
linesfromfile = ""
Dim strEmail As String
Dim bSummaryFound As Boolean
Dim bServicesFound As Boolean
Dim strVerifyBox As String
Dim bMonthlyRateFound As Boolean
Dim bRequestTypeFound As Boolean
Dim strServices As String
Dim bLanguageFound As Boolean
Dim bLastEventDateFound As Boolean
Dim strMonthlyRate As String
Dim bPPVHoldFound As Boolean
Dim strLastEventDate As String
Dim bMDPageIDFound As Boolean
Dim strInstallDate As String
Dim strLanguage As String
Dim bInstallDateFound As Boolean
Dim bNameFound As Boolean
Dim strEventNumDateRange As String
Dim bAddressFound As Boolean
Dim bCityStateZipFound As Boolean
Dim bVerifyBoxFound As Boolean
Dim bSubjectFound As Boolean
Dim strMDPageID As String
Dim bReturnMethodFound As Boolean
Dim bAccountNumFound As Boolean
Dim bReturnDateFound As Boolean
Dim strCRCPIN As String
Dim bCommentsFound As Boolean
Dim bBoxTypeFound As Boolean
Dim strRequestType As String
Dim bBoxQtyFound As Boolean
Dim bCreditAmountFound As Boolean
Dim bConvertersFound As Boolean
Dim bSenderFound As Boolean
Dim bRequestDateFound As Boolean
Dim strStringBefore As String
Dim strName As String
Dim strAddress As String
Dim strCityStateZip As String
Dim strSubject As String
Dim strReturnMethod As String
Dim strAccountNum As String
Dim strCurrentChar As String
Dim strReturnDate As String
Dim strComments As String
Dim strBoxType As String
Dim strBoxQty As String
Dim strCreditAmount As String
Dim strConverterNumbers As String
Dim strSender As String
Dim strRequestDate As String
Dim lngLengthOfEmail As Long
Dim bEventNumDateRangeFound As Boolean
Dim lngCharPointer1 As Long
Dim lngCharPointer2 As Long
Dim dbDatabase As DAO.Database
Dim rsRecordset As DAO.Recordset
Dim bCRCPINFound As Boolean
Dim strPPVHold As String
Dim strSummary As String
If (IsNull(Me.txtEmail.Value)) Then
MsgBox "[Email] field is blank. Please try again.",
vbExclamation + vbOKOnly
Me.txtEmail.SetFocus
Exit Sub
End If
Me.txtStatusBar.Value = "Parsing..."
strEmail = Me.txtEmail.Value
' Initialize
bNameFound = False
bAddressFound = False
bCityStateZipFound = False
bSubjectFound = False
bReturnMethodFound = False
bAccountNumFound = False
bCommentsFound = False
bBoxQtyFound = False
bBoxTypeFound = False
bCreditAmountFound = False
bConvertersFound = False
bReturnDateFound = False
bSenderFound = False
bRequestDateFound = False
strRequestDate = ""
strName = ""
strAddress = ""
strCityStateZip = ""
strSubject = ""
strReturnMethod = ""
strAccountNum = ""
strStringBefore = ""
strComments = ""
strBoxQty = ""
strBoxType = ""
strCreditAmount = ""
strConverterNumbers = ""
strReturnDate = ""
strSender = ""
lngLengthOfEmail = Len(strEmail)
lngCharPointer1 = 0
Do While (Not bSubjectFound)
strCurrentChar = Left(strEmail, 1)
If (strCurrentChar = ":") Then
'Novell
ID--------------------------------------------------------------
If (InStr(strStringBefore, "Date")) Then
If (Not bRequestDateFound) Then
' Get rid of front white space
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Do While (strCurrentChar = " ")
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
' Save address until cr
Do While (strCurrentChar <> vbCr)
strRequestDate = strRequestDate & strCurrentChar
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
bRequestDateFound = True
' Reset string before ':' and move to the next
character
strStringBefore = ""
strEmail = Right(strEmail, (Len(strEmail) - 1))
End If
'Subject-----------------------------------------------------------
ElseIf (InStr(strStringBefore, "ubject")) Then
If (Not bSubjectFound) Then
' Get rid of front white space
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Do While (strCurrentChar = " ")
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
' Save subject until crlf
Do While (strCurrentChar <> "[")
strSubject = strSubject & strCurrentChar
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
bSubjectFound = True
' Reset string before ':' and move to the next
character
strStringBefore = ""
strEmail = Right(strEmail, (Len(strEmail) - 1))
End If
Else
' Reset string before ':' and move to the next character
strStringBefore = ""
strEmail = Right(strEmail, (Len(strEmail) - 1))
End If
Else
' Append this character to string that is before ':'
strStringBefore = strStringBefore & strCurrentChar
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
End If
' ' Advance the character pointer
lngCharPointer1 = lngCharPointer1 + 1
Loop
Do While (Not bMDPageIDFound)
strCurrentChar = Left(strEmail, 1)
If (strCurrentChar = "]") Then
'Novell
ID--------------------------------------------------------------
If (InStr(strStringBefore, "ovell ID")) Then
If (Not bSenderFound) Then
' Get rid of front white space
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Do While (strCurrentChar = " ")
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
' Save address until cr
Do While (strCurrentChar <> "[")
strSender = strSender & strCurrentChar
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
bSenderFound = True
' Reset string before ':' and move to the next
character
strStringBefore = ""
strEmail = Right(strEmail, (Len(strEmail) - 1))
End If
' Else
' ' Reset string before ':' and move to the next character
' strStringBefore = ""
' strEmail = Right(strEmail, (Len(strEmail) - 1))
' End If
' Else
' ' Append this character to string that is before ':'
' strStringBefore = strStringBefore & strCurrentChar
' ' Move to the next character
' strEmail = Right(strEmail, (Len(strEmail) - 1))
' End If
' ' Advance the character pointer
' lngCharPointer1 = lngCharPointer1 + 1
'Loop
' Do While ((lngCharPointer1 <= lngLengthOfEmail))
'And (Not bSummaryFound))
' strCurrentChar = Left(strEmail, 1)
' If (strCurrentChar = "]") Then
'Name--------------------------------------------------------------
ElseIf (InStr(strStringBefore, "s Name")) Then
If (Not bNameFound) Then
' Get rid of front white space
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Do While (strCurrentChar = " ")
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
' Save name until cr
Do While (strCurrentChar <> "[")
strName = strName & strCurrentChar
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
bNameFound = True
' Reset string before ':' and move to the next
character
strStringBefore = ""
strEmail = Right(strEmail, (Len(strEmail) - 1))
End If
'Return
Date--------------------------------------------------------------
ElseIf (InStr(strStringBefore, "Date of Return")) Then
If (Not bReturnDateFound) Then
' Get rid of front white space
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Do While (strCurrentChar = " ")
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
' Save address until cr
Do While (strCurrentChar <> "[")
strReturnDate = strReturnDate & strCurrentChar
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
bReturnDateFound = True
' Reset string before ':' and move to the next
character
strStringBefore = ""
strEmail = Right(strEmail, (Len(strEmail) - 1))
End If
'Return
Method--------------------------------------------------------------
ElseIf (InStr(strStringBefore, "Return Method")) Then
If (Not bReturnMethodFound) Then
' Get rid of front white space
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Do While (strCurrentChar = " ")
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
' Save address until cr
Do While (strCurrentChar <> "[")
strReturnMethod = strReturnMethod & strCurrentChar
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
bReturnMethodFound = True
' Reset string before ':' and move to the next
character
strStringBefore = ""
strEmail = Right(strEmail, (Len(strEmail) - 1))
End If
'Address-------------------------------------------------------------
ElseIf (InStr(strStringBefore, "treet")) Then
If (Not bAddressFound) Then
' Get rid of front white space
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Do While (strCurrentChar = " ")
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
' Save address until cr
Do While (strCurrentChar <> "[")
strAddress = strAddress & strCurrentChar
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
bAddressFound = True
' Reset string before ':' and move to the next
character
strStringBefore = ""
strEmail = Right(strEmail, (Len(strEmail) - 1))
End If
'City, State,
Zip--------------------------------------------------------------
ElseIf (InStr(strStringBefore, "Zip")) Then
If (Not bCityStateZipFound) Then
' Get rid of front white space
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Do While (strCurrentChar = " ")
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
' Save address until cr
Do While (strCurrentChar <> "[")
strCityStateZip = strCityStateZip & strCurrentChar
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
bCityStateZipFound = True
' Reset string before ':' and move to the next
character
strStringBefore = ""
strEmail = Right(strEmail, (Len(strEmail) - 1))
End If
'Account Number-------------------------------------------------------
ElseIf (InStr(strStringBefore, "count Number")) Then
If (Not bAccountNumFound) Then
' Get rid of front white space
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Do While (strCurrentChar = " ")
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
' Save address until cr
Do While (strCurrentChar <> "[")
strAccountNum = strAccountNum & strCurrentChar
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
bAccountNumFound = True
' Reset string before ':' and move to the next
character
strStringBefore = ""
strEmail = Right(strEmail, (Len(strEmail) - 1))
End If
'Install Date--------------------------------------------------------
ElseIf (InStr(strStringBefore, "stall Date")) Then
If (Not bInstallDateFound) Then
' Get rid of front white space
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Do While (strCurrentChar = " ")
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
' Save address until cr
Do While (strCurrentChar <> "[")
strInstallDate = strInstallDate & strCurrentChar
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
bInstallDateFound = True
' Reset string before ':' and move to the next
character
strStringBefore = ""
strEmail = Right(strEmail, (Len(strEmail) - 1))
End If
'Last Known Event
Date-------------------------------------------------------
ElseIf (InStr(strStringBefore, "Known Event Date")) Then
If (Not bLastEventDateFound) Then
' Get rid of front white space
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Do While (strCurrentChar = " ")
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
' Save address until cr
Do While (strCurrentChar <> "[")
strLastEventDate = strLastEventDate &
strCurrentChar
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
bLastEventDateFound = True
' Reset string before ':' and move to the next
character
strStringBefore = ""
strEmail = Right(strEmail, (Len(strEmail) - 1))
End If
'PPV Hold--------------------------------------------------------------
ElseIf (InStr(strStringBefore, "PV Hold")) Then
If (Not bPPVHoldFound) Then
' Get rid of front white space
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Do While (strCurrentChar = " ")
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
' Save address until cr
Do While (strCurrentChar <> "[")
strPPVHold = strPPVHold & strCurrentChar
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
bPPVHoldFound = True
' Reset string before ':' and move to the next
character
strStringBefore = ""
strEmail = Right(strEmail, (Len(strEmail) - 1))
End If
'Monthly
Rate--------------------------------------------------------------
ElseIf (InStr(strStringBefore, "onthly Rate")) Then
If (Not bMonthlyRateFound) Then
' Get rid of front white space
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Do While (strCurrentChar = " ")
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
' Save address until cr
Do While (strCurrentChar <> "[")
strMonthlyRate = strMonthlyRate & strCurrentChar
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
bMonthlyRateFound = True
' Reset string before ':' and move to the next
character
strStringBefore = ""
strEmail = Right(strEmail, (Len(strEmail) - 1))
End If
'Current
Services--------------------------------------------------------------
ElseIf (InStr(strStringBefore, "urrent Services")) Then
If (Not bServicesFound) Then
' Get rid of front white space
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Do While (strCurrentChar = " ")
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
' Save address until cr
Do While (strCurrentChar <> "[")
strServices = strServices & strCurrentChar
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
bServicesFound = True
' Reset string before ':' and move to the next
character
strStringBefore = ""
strEmail = Right(strEmail, (Len(strEmail) - 1))
End If
'Request
Type--------------------------------------------------------------
ElseIf (InStr(strStringBefore, "ype of Request")) Then
If (Not bRequestTypeFound) Then
' Get rid of front white space
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Do While (strCurrentChar = " ")
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
' Save address until cr
Do While (strCurrentChar <> "[")
strRequestType = strRequestType & strCurrentChar
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
bRequestTypeFound = True
' Reset string before ':' and move to the next
character
strStringBefore = ""
strEmail = Right(strEmail, (Len(strEmail) - 1))
End If
'Language------------------------------------
ElseIf (InStr(strStringBefore, "anguage")) Then
If (Not bLanguageFound) Then
' Get rid of front white space
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Do While (strCurrentChar = " ")
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
' Save address until cr
Do While (strCurrentChar <> "[")
strLanguage = strLanguage & strCurrentChar
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
bLanguageFound = True
' Reset string before ':' and move to the next
character
strStringBefore = ""
strEmail = Right(strEmail, (Len(strEmail) - 1))
End If
'CRC Pin Instructions-------------------------------------------------
ElseIf (InStr(strStringBefore, "Pin Instructions")) Then
If (Not bCRCPINFound) Then
' Get rid of front white space
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Do While (strCurrentChar = " ")
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
' Save address until cr
Do While (strCurrentChar <> "[")
strCRCPIN = strCRCPIN & strCurrentChar
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
bCRCPINFound = True
' Reset string before ':' and move to the next
character
strStringBefore = ""
strEmail = Right(strEmail, (Len(strEmail) - 1))
End If
'Event Numbers/Date Range-----------------------
ElseIf (InStr(strStringBefore, "Date Range")) Then
If (Not bEventNumDateRangeFound) Then
' Get rid of front white space
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Do While (strCurrentChar = " ")
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
' Save address until cr
Do While (strCurrentChar <> "[")
strEventNumDateRange = strEventNumDateRange &
strCurrentChar
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
bEventNumDateRangeFound = True
' Reset string before ':' and move to the next
character
strStringBefore = ""
strEmail = Right(strEmail, (Len(strEmail) - 1))
End If
'Summary----------------------------------------
ElseIf (InStr(strStringBefore, "ummary")) Then
If (Not bSummaryFound) Then
' Get rid of front white space
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Do While (strCurrentChar = " ")
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
' Save address until cr
Do While (strCurrentChar <> "[")
strSummary = strSummary & strCurrentChar
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
bSummaryFound = True
' Reset string before ':' and move to the next
character
strStringBefore = ""
strEmail = Right(strEmail, (Len(strEmail) - 1))
End If
'BoxType--------------------------------------------------------------
ElseIf (InStr(strStringBefore, "Type Of Box")) Then
If (Not bBoxTypeFound) Then
' Get rid of front white space
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Do While (strCurrentChar = " ")
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
' Save address until cr
Do While (strCurrentChar <> "[")
strBoxType = strBoxType & strCurrentChar
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
bBoxTypeFound = True
' Reset string before ':' and move to the next
character
strStringBefore = ""
strEmail = Right(strEmail, (Len(strEmail) - 1))
End If
'BoxQty--------------------------------------------------------------
ElseIf (InStr(strStringBefore, "How Many Boxes")) Then
If (Not bBoxQtyFound) Then
' Get rid of front white space
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Do While (strCurrentChar = " ")
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
' Save address until cr
Do While (strCurrentChar <> "[")
strBoxQty = strBoxQty & strCurrentChar
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
bBoxQtyFound = True
' Reset string before ':' and move to the next
character
strStringBefore = ""
strEmail = Right(strEmail, (Len(strEmail) - 1))
End If
'CreditAmount--------------------------------------------------------------
ElseIf (InStr(strStringBefore, "Amount To Credit")) Then
If (Not bCreditAmountFound) Then
' Get rid of front white space
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Do While (strCurrentChar = " ")
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
' Save address until cr
Do While (strCurrentChar <> "[")
strCreditAmount = strCreditAmount & strCurrentChar
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
bCreditAmountFound = True
' Reset string before ':' and move to the next
character
strStringBefore = ""
strEmail = Right(strEmail, (Len(strEmail) - 1))
End If
'ConverterNumbers--------------------------------------------------------------
ElseIf (InStr(strStringBefore, "ConverterNumbers")) Then
If (Not bConvertersFound) Then
' Get rid of front white space
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Do While (strCurrentChar = " ")
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
' Save address until cr
Do While (strCurrentChar <> "[")
strConverterNumbers = strConverterNumbers &
strCurrentChar
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
bConvertersFound = True
' Reset string before ':' and move to the next
character
strStringBefore = ""
strEmail = Right(strEmail, (Len(strEmail) - 1))
End If
'Comments--------------------------------------------------------------
ElseIf (InStr(strStringBefore, "Comments")) Then
If (Not bCommentsFound) Then
' Get rid of front white space
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Do While (strCurrentChar = " ")
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
' Save address until cr
Do While (strCurrentChar <> "[")
strComments = strComments & strCurrentChar
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
bCommentsFound = True
' Reset string before ':' and move to the next
character
strStringBefore = ""
strEmail = Right(strEmail, (Len(strEmail) - 1))
End If
'Box Verification-------------------------------
ElseIf (InStr(strStringBefore, "Verification by Customer"))
Then
If (Not bVerifyBoxFound) Then
' Get rid of front white space
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Do While (strCurrentChar = " ")
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
' Save address until cr
Do While (strCurrentChar <> "[")
strVerifyBox = strVerifyBox & strCurrentChar
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
bVerifyBoxFound = True
' Reset string before ':' and move to the next
character
strStringBefore = ""
strEmail = Right(strEmail, (Len(strEmail) - 1))
End If
'MD Page
ID--------------------------------------------------------------
ElseIf (InStr(strStringBefore, "Page ID")) Then
If (Not bMDPageIDFound) Then
' Get rid of front white space
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Do While (strCurrentChar = " ")
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
' Save city, state, zip until cr
Do While (strCurrentChar <> "[")
strMDPageID = strMDPageID & strCurrentChar
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
strCurrentChar = Left(strEmail, 1)
lngCharPointer1 = lngCharPointer1 + 1
Loop
bMDPageIDFound = True
' Reset string before ':' and move to the next
character
strStringBefore = ""
strEmail = Right(strEmail, (Len(strEmail) - 1))
End If
'--------------------------------------------------------------------------------
End If
End If
' Else
' Append this character to string that is before ':'
strStringBefore = strStringBefore & strCurrentChar
' Move to the next character
strEmail = Right(strEmail, (Len(strEmail) - 1))
'I COMPARE THESE LINES------------------------------
' Advance the character pointer
lngCharPointer1 = lngCharPointer1 + 1
Loop
' Clear white space, from right
If (bNameFound) Then
strCurrentChar = Right(strName, 1)
Do While (strCurrentChar = " ")
' Advance 1 char, from right
strName = Left(strName, (Len(strName) - 1))
strCurrentChar = Right(strName, 1)
Loop
End If
If (bSenderFound) Then
strCurrentChar = Right(strSender, 1)
Do While (strCurrentChar = " ")
' Advance 1 char, from right
strSender = Left(strSender, (Len(strSender) - 1))
strCurrentChar = Right(strSender, 1)
Loop
End If
If (bSubjectFound) Then
strCurrentChar = Right(strSubject, 1)
Do While (strCurrentChar = " ")
' Advance 1 char, from right
strSubject = Left(strSubject, (Len(strSubject) - 1))
strCurrentChar = Right(strSubject, 1)
Loop
End If
If (bReturnMethodFound) Then
strCurrentChar = Right(strReturnMethod, 1)
Do While (strCurrentChar = " ")
' Advance 1 char, from right
strReturnMethod = Left(strReturnMethod, (Len(strReturnMethod) -
1))
strCurrentChar = Right(strReturnMethod, 1)
Loop
End If
If (bAccountNumFound) Then
strCurrentChar = Right(strAccountNum, 1)
Do While (strCurrentChar = " ")
' Advance 1 char, from right
strAccountNum = Left(strAccountNum, (Len(strAccountNum) - 1))
strCurrentChar = Right(strAccountNum, 1)
Loop
End If
If (bAddressFound) Then
strCurrentChar = Right(strAddress, 1)
Do While (strCurrentChar = " ")
' Advance 1 char, from right
strAddress = Left(strAddress, (Len(strAddress) - 1))
strCurrentChar = Right(strAddress, 1)
Loop
End If
If (bCityStateZipFound) Then
strCurrentChar = Right(strCityStateZip, 1)
Do While (strCurrentChar = " ")
' Advance 1 char, from right
strCityStateZip = Left(strCityStateZip, (Len(strCityStateZip) -
1))
strCurrentChar = Right(strCityStateZip, 1)
Loop
End If
If (bReturnDateFound) Then
strCurrentChar = Right(strReturnDate, 1)
Do While (strCurrentChar = " ")
' Advance 1 char, from right
strReturnDate = Left(strReturnDate, (Len(strReturnDate) - 1))
strCurrentChar = Right(strReturnDate, 1)
Loop
End If
If (bBoxTypeFound) Then
strCurrentChar = Right(strReturnDate, 1)
Do While (strCurrentChar = " ")
' Advance 1 char, from right
strReturnDate = Left(strReturnDate, (Len(strReturnDate) - 1))
strCurrentChar = Right(strReturnDate, 1)
Loop
End If
If (bBoxQtyFound) Then
strCurrentChar = Right(strReturnDate, 1)
Do While (strCurrentChar = " ")
' Advance 1 char, from right
strReturnDate = Left(strReturnDate, (Len(strReturnDate) - 1))
strCurrentChar = Right(strReturnDate, 1)
Loop
End If
If (bCreditAmountFound) Then
strCurrentChar = Right(strReturnDate, 1)
Do While (strCurrentChar = " ")
' Advance 1 char, from right
strReturnDate = Left(strReturnDate, (Len(strReturnDate) - 1))
strCurrentChar = Right(strReturnDate, 1)
Loop
End If
If (bCommentsFound) Then
strCurrentChar = Right(strComments, 1)
Do While (strCurrentChar = " ")
' Advance 1 char, from right
strComments = Left(strComments, (Len(strComments) - 1))
strCurrentChar = Right(strComments, 1)
Loop
End If
Me.txtStatusBar.Value = "Parsing...Complete."
'Debug.Print _
' "Name Found: " & bNameFound & vbCrLf & _
' "Name: " & strName & vbCrLf & vbCrLf & _
' "Address Found: " & bAddressFound & vbCrLf & _
' "Address: " & strAddress & vbCrLf & vbCrLf & _
' "City, State, Zip Found: " & bCityStateZipFound & vbCrLf & _
' "City, State, Zip: " & strCityStateZip
If (bNameFound And bAddressFound And bCityStateZipFound And
bSubjectFound And bAccountNumFound) Then
Me.txtStatusBar.Value = "Creating record..."
' Found all the fields
Set dbDatabase = CurrentDb()
Set rsRecordset = dbDatabase.OpenRecordset("tblCustomers")
' Create a new record with parsed info
rsRecordset.AddNew
rsRecordset.Fields(1).Value = UCase(strRequestDate)
rsRecordset.Fields(2).Value = UCase(strName)
rsRecordset.Fields(3).Value = UCase(strSender)
rsRecordset.Fields(4).Value = UCase(strAddress)
rsRecordset.Fields(5).Value = UCase(strCityStateZip)
' rsRecordset.Fields(6).Value = UCase(strSubject)
rsRecordset.Fields(7).Value = UCase(strReturnMethod)
rsRecordset.Fields(8).Value = UCase(strAccountNum)
rsRecordset.Fields(9).Value = UCase(strReturnDate)
rsRecordset.Fields(10).Value = UCase(strBoxType)
rsRecordset.Fields(11).Value = UCase(strBoxQty)
rsRecordset.Fields(12).Value = UCase(strCreditAmount)
rsRecordset.Fields(13).Value = UCase(strConverterNumbers)
rsRecordset.Fields(14).Value = UCase(strComments)
rsRecordset.Update
rsRecordset.Close
Set rsRecordset = Nothing
dbDatabase.Close
Set dbDatabase = Nothing
Else
'Could not find all or some of the fields
'Add incomplete record to exceptions table for manual processing.
Set dbDatabase = CurrentDb()
Set rsRecordset = dbDatabase.OpenRecordset("tblExceptions")
rsRecordset.AddNew
rsRecordset.Fields(1).Value = Me.txtEmail.Value
rsRecordset.Fields(2).Value = Now()
rsRecordset.Update
rsRecordset.Close
Set rsRecordset = Nothing
dbDatabase.Close
Set dbDatabase = Nothing
'MsgBox "Could not find a field. Please try again.", vbExclamation
+ vbOKOnly
End If
' Clear email field and get ready for another one
Me.txtEmail.Value = Null
Me.txtEmail.SetFocus
Kill "C:\MailSave\GetInfo.txt"
Loop
DoCmd.SetWarnings False
DoCmd.OpenQuery "qry_LoadWork", acViewNormal, acEdit
DoCmd.OpenQuery "qry_UpdateBCSubject", acViewNormal, acEdit
DoCmd.OpenQuery "qry_UpdateCRSubject", acViewNormal, acEdit
DoCmd.OpenQuery "qry_DeleteParsed", acViewNormal, acEdit
Me.txtStatusBar.Value = "Creating record...Complete."
Exit_cmdParse_Click:
Exit Sub
End Sub
---------------------------------------------------------------------------------SAMPLE
TEXT FILE
Date: 2006-08-10-13-38-47
To: Stephen_Ng
From: <KDB Shared Services>
Subject: Box Credit - 501756
[Date]
Thu Aug 10 13:38:45 EDT 2006
[Novell ID]
SNG
[Subject]
Box Credit - 501756
[Customer's Name]
DIOMARIS GRULLON
[Street]
69 STUYVESANT AV APT 3
[City, State, Zip]
BROOKLYN NY, 11221
[Account Number]
07836-105575-02
[Corp]
7836
[Amount to Credit]
123
[Return Method]
Tech
[Tech Number]
123
[Date Returned]
08/10/2006
[Type of Equipment]
Digital
[Number of Boxes]
1
[Converter Numbers]
123
[Comments]
test
[Control Number]
658006
|
| | 1 answer | Add comment |
|
| Retrieve primary key SueA 23:21:54 |
| | Can anyone help me with the code to retrieve the name of the PrimaryKey
field of the current table?
For instance:
qryMfrsAndBrands is based on the tables: tblBrands and tblMfrs
formMfrsBrands is based on qryMfrsAndBrands
fieldBrandName is on the form and comes from tblBrands via
qryMfrsAndBrands.
I'd like to retrieve the name of the primary key field for tblBrands.
Thanks again.
I'm still very new at this - but not as new as I was yesterday!
-Sue
|
| | 2 answer | Add comment |
|
| skip one query during process Eric 23:17:49 |
| | If i run a query and the process stuck how to i get out from that
query. I can use Ctrl+Alt+Del but it terminate all of the program. I
wrote multiple queries one by one in my program. Is it possible i skip
one of the query during process.
Thanks.
|
| | Add comment |
|
| Option-button-choice enables fields Filips Benoit 23:03:48 |
| | Dear All,
Table1
Field1: Name
Field2: Gender
Field3:Question1ForMale
Field4:Question2ForMale
Field5:Question3ForMale
Field6:Question1ForFemale
Field7:Question2ForFemale
Field8:Question3ForFemale
A form ( source = Table1) having all fields (questions for male and female)
After selecting option button 'Male' (fields2) the questions for female (
fields6>8) should be enabled, locked.
I should do it using code, BUT it was an exam-question and they never used
code.
Can it be done without code? How?
Thanks,
Filip
|
| | 6 answers | Add comment |
|
| Re-order a Combo Menu Feeman 23:01:23 |
| | I have a combo field in access 97, which you select customers from, but
if you add a new customer into the customer accounts it put them at the
bottom of the combo menu. Is there a way to make all new entries be
placed in order, so that when you look at the combo box it is already
in alphabetical order. I have gone into the table and put it in order,
but when you use the combo menu it is out of order again.
|
| | 3 answer | Add comment |
|
| RE-Indexing a MS Access database Guest 22:58:55 |
| | All
I have a database that I am taking over the maintenance of - the
primary index in this relational database is a field JP_ID which was
defined as an AUTO NUMBER type field.........now the problem is that
there are huge gaps in the JP_ID field and the number has even gon
negative (e.g. -144456766678)
There are subordinate tables that use the JP_ID.......
QUESTION: Is there a way to re-index the primary table and then send
those new indexes down to the subordinate table??? Is there a package
or script??
Thanks!!
|
| | 2 answer | Add comment |
|
| Uppercase to Mixed Case Edit in Field Sara 22:55:48 |
| | I have a table where a few of the users entered vendor names ALL IN
UPPER CASE. I have created forms to edit the data, but I can't seem to
allow changing JOE SMITH to Joe Smith.
What to I have to do to have the user change the name, but keep the
key? Overall, I think any "edits" simply add a new record, rather than
change the existing record.
The form is simple - Choose the vendor from the drop-down list.
After Update, a new field displays: Vendor: [and the chosen vendor in
the field]
If the user wishes to Add a new vendor or Delete the current one, they
choose a button on the bottom of the screen and that works just fine.
The "edit" or "Change" is what doesn't work.
Don't know what code to post, so I didn't post any.
Thanks.
Sara
|
| | 8 answers | Add comment |
|
| Looping Issue with DoCmd.OutputTo Ryan 22:06:03 |
| | Hello. I was hoping that someone may be able to assist with an issue
that I am experiencing. I have created an Access DB which imports an
Excel File with a particular layout and field naming. Next the user
can go into a Form which basically a dynamic query with a friendly
interface that eventually outputs a table that is stored in the DB as
well as exported to a CSV file. The CSV file is then used with a
vendor solution to fill in company information (prevents manual entry).
So in the code, I originally wrote a Do While Statement that stores all
the dynamic information that is used to generate a SQL Statement that
builds the table from the recordset. After the table is built and in
the Do While Statement, I have the following If Then Statement:
If strBuildCounter < strBuildRecordset Then
DoCmd.TransferText acExportDelim,, "Export_" & [strTable01],
"C:\My Data\" &
[strTable01] & ".csv", True
DoCmd.GoToRecord , , acNext
Else
DoCmd.TransferText acExportDelim,, "Export_" &
[strTable01], "C:\My Data\" & [strTable01] & ".csv", True
End If
With this and the rest of the code in the event, the process works
great for building all the tables that are in the recordset. However,
after viewing the output in Excel, I learned that TransferText doesn't
keep the formatting that is in the table. After reading through some
of the articles here, I tried switching to using the following
DoCmd.Output line instead of the DoCmd.TransferText:
DoCmd.OutputTo acOutputTable, "Export_" & [strTable01], acFormatXLS,
"C:\My Data\" & [strTable01] & ".csv", False, ""
So the output that I get is exactly what I wanted from a formatting
perspective. However, for some reason now it is not building all of the
tables and CSV files. For example, if I have 6 records it will build
and export Table 1, Table 2, and Table 6. I am totally baffled because
the only thing that changed from is the DoCmd.TransferText line was
commented out and the new DoCmd.OutputTp line was added in the same
location in the If Then statement.
I threw in message boxes to see what the loop counter and total
recordset count were each time the loop ran through. For both the
DoCmd.TransferText and DoCmd.OutputTo line, it would say 1-6, 2-6, 3-6,
4-6, 5-6, and the 6-6. The one thing I did notice is that when I am
running the DoCmd.TransferText it is moving through the form records
fine. When I run the DoCmd.OutputTo, it will be on Form Record 1 and
make the Table 1. It will then advance to Form Record 2, make Table 2.
Then it will go back to Form Record 1 and remake Table 1. It will then
go to Form Record 2 again and remake Table 2. Then it will go back to
Form Record 1 and remake Table 1. Finally, it will go to Form Record 6
and make Table 6 and then be done.
If I had just done the DoCmd.OutputTo originally and never tried the
DoCmd.TransferText, I would assume the issue was in the looping code.
However, since it works with DoCmd.TransferText I am assuming that it
is something else that I am either doing wrong or a Microsoft issue.
I greatly appreciate any assistance on this.
Thanks!
Ryan
|
| | Add comment |
|
| Change Default setting for the Search tool? Robert 5032 21:24:07 |
| | when our users press "Search and Replace" all is fine exept that they
ALWAYS have
to change the criteria "Match the hole field" to "Part of the field"
(ruffly spelled...(swedish lang)).
-Is there anyway to change/ add some code so that the search dialogbox
shows with this already done? That would make me happy
Thank you for any clue!
Robert
|
| | 6 answers | Add comment |
|
| Back-End Security question Tammy 20:44:29 |
| | Hello -
I have read many threads regarding back-end security and have found
them all useful. I have a couple of (what seem to be basic) questions:
I have a secured front-end and back-end database. (I did use the
security wizard for this.) By using the shortcuts provided, both files
require a password to get in.
Here is the problem: if I try to access the front-end through Windows
Explorer, I do get the prompt stating it cannot be opened. However, I
am able to open the back-end without a prompt.
I'm pretty sure I read a thread that basically stated this is just the
way it is when it comes to the back-end file. Is this true? Can anyone
locate the back-end and have access to the data without being prompted
for their username and password? (Which would allow them access to the
file with full permissions, instead of adhering to the permissions
assigned in the security file.)
I also read that by assigning a password to the back-end file, only the
people with that password would actually be able to open it through
Windows Explorer, which could be a solution. However, I have users who
have permissions to add, delete, update, etc. records, thus, requiring
access to the tables through their linked front-end copy. Wouldn't this
require everyone to have the password to the back-end in order to work
with the data through their front-end file? (In effect, still allowing
everyone access to my back-end through Windows Explorer with full
permissions?)
Thanks for any information you can provide. I hope my questions are
clear.
Tammy
|
| | 1 answer | Add comment |
|
| Decompile Fails rdemyan via AccessMonster.com 20:40:00 |
| | Has anyone experienced the decompile process failing.
I've got the following shortcut which points to the Logon application for my
main application:
"C:\Program Files\Microsoft Office\OFFICE11\msaccess.exe" "C:\Documents and
Settings\Owner\My Documents\Logon\Client 2003 Format\MYAPP Logon.mdb"
/decompile
The file opens just fine. But when I go to recompile it, the Compile command
is grayed out.
I decompile my main app all the time and I've never had a problem with that.
Any ideas?
Thank you.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200608/1
|
| | Add comment |
|
| Names and addresses in report Janet 20:16:01 |
| | I am trying to set up a report that looks like a letter, so like
Contactaddress1
Contactaddress2
Contactaddress3
Contactaddress4
Postcode
This works fine but looks messy if, say, there is no Contactaddress4.
Is there some way that if a box is blank, there is no space left, as
when mailmerging using Word.
Thanks
Janet
|
| | 2 answer | Add comment |
|
| blank records in a relation table....can't figure out why Sparks 20:05:18 |
| | I am trying to fix a database that someone did about 4 yrs ago in
access97.
The main table just contains demographics and is on the main form of
the database.
It has a subform on a tab that contains the other information.
it is set 1 to 1 in the relationships.
simplify if I can
table 1 and table2
main form table 1
tab
subform table 2
when you create a new record and enter a id number on the main form
and exit.
you have the correct info in table 1 and table 2 has a blank record.
you again put in info in main form and enter data in in the subform.
you have a record in table 1, a record with the linked id in table
to...all looks ok but then I again have a blank record in table 2.
I tried to see if they had some type of requery in the forms or maybe
even some default value or something but it seems that when you add
a record and type in anything in the main form a blank record is
created in the subform table at that instant.. and when you enter
something in the subform a correct linked record is created.
but for the life of me I can't figure out why the blank record is
created in the subform table.
any ideas are greatly appreciated
Jerry
|
| | 4 answer | Add comment |
|
| New record in subForm on mainForm load Guest 19:36:52 |
| | I have a Starting form ("frmStart") and a main form ("frmCustomer")
inside frmCustomer are two subform, one with it's own subform. So
frmCustomer has two subforms ("sbfArtist" and "sbfAddress") inside
subform "sbfArtist" is another subform, "sbfTitle"... I'll try to
create a visible heiarchy:
frmStart
----frmCustomer
--------sbfAddress
--------sbfArtist
------------sbfTitle
I hope that helps set up the picture...
Anyway, inside frmStart is a command button that allows the user to
enter a new Address in frmCustomer. The CustomerID is readily available
form frmStart. I don't want to do this with SQL code as I need the
ability to allow the user to back out of creating a new entry and to
edit the fields before it's updated. Inside frmCustomer_Load() I tried
using RunCommand acCmdRecordsGoToNew after setting the focus to the
subform but that did not work.
I load and display the correct CustomerID with:
Private Sub Form_Load() ' frmCustomer
If Me.OpenArgs = "NewBillAddress" Then
With Me.RecordsetClone
.FindFirst "[CustomerID]=" & Forms!frmStart!Customer
If Not .NoMatch Then
Me.Bookmark = .Bookmark
With Me.[sbfAddress].Form
Me.sbfAddress.Form.SetFocus
Me.sbfAddress.Form.addNewRecord
End With
End If
End With
End If
End Sub
That executes without error but fails to set focus to a New record in
sbfAddress
After getting this to work I will attempt to create similar
functionality to the 2-level-subform (frmCustomer>>sbfArtist>>sbfTitle)
and I'm sure I can figure that out if I can just get this to work.
Any help would be greatly appreciated. Thanks!
|
| | 1 answer | Add comment |
|
| Crosstabs impossible on memo fields as 'value'? Emily Jones 19:08:34 |
| | This:
TRANSFORM First([_allText].Text) AS FirstOfText
SELECT [_allText].Expr1000
FROM _allText
GROUP BY [_allText].Expr1000
PIVOT [_allText].LetterSectionID;
hangs the application if _allText.Text is a memo field. But it's fine if
it's a text field.
Is it not possible to do what I'm trying to do?
Thanks, Emily
|
| | 3 answer | Add comment |
|
| Launching Access .mdb file directly vs. launching with VBA Microb0x 18:44:28 |
| | Is there any difference in the way an Access .mdb file is launched from
directly double-clicking the file through windows explorer versus using
code within another Access file to launch the same file?
Here is my situation:
I have an application that when launched does a check whether or not
its the most current version of the Front End that I have in
production. If the front end is out of date, it closes, and launches
another access .mdb file that serves as an update utitlity. My problem
is when this update utility is launched.
I have code within this update utility to hide the main Access
container window, API courtesy of Dev Ashish, for example:
fSetAccessWindow(SW_HIDE)
I know that there has to be an active form already open to call this
function so I'm opening a hidden form upon open of the Access file,
using the timer to wait 1/2 second then opening my main form and
calling that function.
If I just use windows explorer to launch this update utility directly
it works like a charm. However when I try to launch this utility from
within my other Access file I get an error:
"Cannot hide Access unless a form is on the screen"
I have tried two methods of launching this update utility from within
the other Access file:
Method 1:
Set objAccess = New Access.Application
objAccess.OpenCurrentDatabase "C:\..." ' valid path is there in my code
Method 2:
Application.Followhyperlink "C:\..." ' valid path was used here as
well.
Both methods yield the above error...
Any suggestions or can anyone give any insight into what differences
there are from just double-clicking the file or launching via code?
|
| | Add comment |
|
| Record Source?? Dr. Hung 18:32:24 |
| | Hey I have a form with many fields in it from several different tables.
Nomatter what I seem to try in the record source area in the properties
of the form All the information will not display correctly.
Should I have a query for this and If so what should it be, or what
code should go in the box??
I'd appriciate any help thanks!!
|
| | 1 answer | Add comment |
|
| How can I do this please Eric 18:03:33 |
| | Hi,
Please can anyone tell me how to achieve multiple inserts using the key
value from a newly inserted record of one table into several other tables.
I have a company table, an address table, a contact table.
when I insert into the company table, I also want to insert the new id
into the address and contact tables under companyID field.
The primary key in the company table is auto number, so the new key is
not known until it is inserted.
is it possible to retrieve the new key as a resultset of the insert
statement so I can then use this to insert into the other tables?
Any help appreciated
Yeric
|
| | 4 answer | Add comment |
|
| seeking asistance with query Welie 17:56:54 |
| | I am trying to do a query to produce a list of employee and their
telephone numbers (IF ANY) which joins on 3 tables. The complication
is the 3rd table, the table containing telephone numbers. That table
has both cellular and regular numbers. The type of phone number is
indicated by a column -phoneType) containing the value 0 for regular
numbers and 1 for cells.
I am getting errors about ambigous outer joins, or only getting results
where both types of numbers exist. I am pretty lost! Any help much
appreciated.
I have the following 3 tables in a database
Contact table, employee table, PhoneNumberstable. The employee and
PhoneNumberstable have a column called contact ID which is a foreign
key to the contact table.
For each employee, I need to select the last name from the contact
table and then regular tel number (if it exists) and cell phone number
(if it exists). I want all the info in a single row, not multiple rows.
I need something like this...
LastName Phone (0) CellPhone (1)
ABC (123)555-1212 NULL
DEF NULL (000)555-1212
GHI (123)456-7890 (000)123-4567
JKL NULL NULL
The following query gives 1 row for each contactID/phone number match.
Again I want 1 row for each contact.
SELECT Contacts.FirstName, Contacts.LastName, PhoneNumbers.phoneNum
FROM (Contacts INNER JOIN employee ON Contacts.ContactID =
employee.ContactID) LEFT JOIN PhoneNumbers ON Contacts.ContactID =
PhoneNumbers.ContactID;
|
| | 2 answer | Add comment |
|
| recordset AddNew primary key issue Guest 17:55:29 |
| | hello,
I am wirting a function in VBA that adds a new record to an existing
database. If i try to add a record with a primary key that already
exists in the database, i get an error. I was wondering if there was
some way I could make AddNew overwrite any old entry with the same
primary key...I was hoping I wouldn't have to iterate through the
entire database each time I add an entry.
thanks
|
| | 5 answers | Add comment |
|
| Determine Computer Name That is Accessing File Vincent 17:37:39 |
| | I have been trying to find some API routines that will allow me to
determine the name of the computer that is accessing a file on a
server. I have found the NetFileEnum call (returns the names of the
files in use and the names of the users accessing them). I have also
found the NetConnectionEnum call (returns the name of the computer that
is accessing a share). I do not see any way of correlating the data
that these two api calls return. Can anyone point me in the right
direction? Thanks.
-Vincent
|
| | 2 answer | Add comment |
|
| Retrieve the table name behind a control on a subform SueA 16:25:25 |
| | Can anyone help me with the code to retrieve the name of the table that
a control on a form (that's built off a query) refers to?
For instance:
qryMfrsAndBrands is based on the tables: tblBrands and tblMfrs
formMfrsBrands is based on qryMfrsAndBrands
fieldBrandName is on the form and comes from tblBrands via
qryMfrsAndBrands.
I've been trying to retrieve the name of the table by using MsgBox with
assorted code.
So far I've been able to retrieve the query name, the field name, and
all of the table names in the database one after the other - but I
can't figure out how to retrieve just the one table name.
What I want to use this for is tracking changes in my database. It
seems to me if I can retrieve the field and table names during a
beforeUpdate event, I can use the same code for all fields and all
tables instead of having to code each one individually.
Any help at all would be greatly appreciated - I'm kind of new at this.
OK, I'm VERY new at this.
-Sue
|
| | 2 answer | Add comment |
|
| Error: Invalid Procedure call or argument Eric 14:28:06 |
| | I run a program which read emails from a text file. There is some thing
invalid in one or more text file. When program is busy doing parsing it
shows that error and process stop. I dont know which text file has that
error. Is it possible that i can handle that error during processing
some thing i mean ignore line, email so that my process will not be
interrupt.
Error: Invalid Procedure call or argument
Run time error 5
I want to handle this error by means of skip that line, email or any
thing but not to stop the parsing.
|
| | 5 answers | Add comment |
|
| Avoid copying duplicate contacts to outlook from access Charliej2001@Googlemail.Com 14:12:22 |
| | Hi all
My access database has import/export capabiltiy of contact details
between outlook. The database is getting big now (1000+ contacts) and
so are the outlook address books that have the contacts info in them.
When I export contacts from access to outlook, it takes a long time
because there are so many contacts in the Outlook address book, and it
is checking all of these against the contacts coming in, so as to avoid
duplicates. Can anyones suggest a faster way of checking to see if the
contact already exists in outlook, and so wont be duplicated?
This is how im doing it at the moment
Sub ExportAllContactsToOutlook()
Dim MainContactRST As DAO.Recordset
Set MainContactRST = CurrentDb.OpenRecordset("Contact Details")
'Set up outlook objects
Dim ol As New Outlook.Application
Dim olns As Outlook.Namespace
Dim cf As Outlook.MAPIFolder
Dim c As Outlook.ContactItem
Dim cCheck As Outlook.ContactItem
Dim objItems As Outlook.Items
Dim Prop As Outlook.UserProperty
Set olns = ol.GetNamespace("MAPI")
Set cf = olns.GetDefaultFolder(olFolderContacts)
Set objItems = cf.Items
iNumContacts = objItems.Count
With MainContactRST
.MoveFirst
'Loop through the Contact Details records.
Do While Not .EOF
If iNumContacts <> 0 Then
For i = 1 To iNumContacts
If TypeName(objItems(i)) = "ContactItem" Then
Set cCheck = objItems(i)
If cCheck.CompanyName = MainContactRST!Company
_
And cCheck.FirstName = MainContactRST!FirstName
_
And cCheck.LastName = MainContactRST!LastName _
And cCheck.JobTitle = MainContactRST!JobTitle
Then
GoTo ContactAlreadyExists
End If
End If
Next i
End If
'Create a new Contact item.
Set c = ol.CreateItem(olContactItem)
'Specify which Outlook form to use.
c.MessageClass = "IPM.Contact"
'Add all items about contact from Access table to Outlook
address book
If MainContactRST!Company <> "" Then c.CompanyName =
MainContactRST!Company
If MainContactRST!FirstName <> "" Then c.FirstName =
MainContactRST!FirstName
If MainContactRST!MiddleName <> "" Then c.MiddleName =
MainContactRST!MiddleName
If MainContactRST!LastName <> "" Then c.LastName =
MainContactRST!LastName
If MainContactRST!Title <> "" Then c.Title =
MainContactRST!Title
If MainContactRST!Suffix <> "" Then c.Suffix =
MainContactRST!Suffix
If MainContactRST!JobTitle <> "" Then c.JobTitle =
MainContactRST!JobTitle
.
.
.
.
.
If MainContactRST!WebPage <> "" Then c.WebPage =
MainContactRST!WebPage
' Save and close the contact.
c.Save
ContactAlreadyExists:
.MoveNext
Loop
MainContactRST.Close
Set MainContactRST = Nothing
Set olns = Nothing
Set cf = Nothing
Set objItems = Nothing
MsgBox "Finished exporting to Outlook"
End With
End Sub
|
| | 1 answer | Add comment |
|
|
