VB in Access

[Cuc Tu]

Once again, I'm an uber novice well over my head...

I'm using VB to "publish" data from my simple database.

One field is a date field that I have defined to show in week number format (ww). The problem is that, while the date shows properly in Access Tables and Forms, the output text file is showing the standard date format...

I think these are the relevant code lines (not complete code):




Function WriteDocumentHTML(strTextFilePath As String)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim fso As Object
Dim fsoFile As Object
Dim strSQL As String

Dim strDateAdded As String


'Define DateAdded field's format (does not work):
'strDateAdded = Format(DateAdded, "ww")



'Set date format (this does not work!):
'strSQL = "SELECT * FROM [Document Table]" _
'& " (Format[Date Added],"ww");"


With rst
Do While .EOF = False


strDateAdded = ![Date Added]

.MoveNext

fsoFile.WriteLine "Date: " & strDateAdded

Loop
End With

fsoFile.Close
rst.Close

End Function



The date comes out as

Date: 7/23/2009

and I need

Date: 30

[Paul Komski]

The complete code would be more helpful.

I think what you basically need to do is to define the formatted field by name and not keep reiterating the Format[Date Added],"ww"); nor do you need to Select All.

Thus you could try an SQL statement along the lines of:

strSQL = "SELECT Format(DateAdded,'ww') AS WeekNum FROM [Document Table];"

Use single quotes round ww or the string gets broken. Then use your own defined field (WeekNum or [WeekNum] in this example) for the output.

Document Table must be inside square brackets because it includes white space. In General try to avoid all white space in field, table, form, etc names - it will simplify much encoding down the line. DocumentTable is less likely to confuse than [Document Table]

[Cuc Tu]

Thanks Paul. I'll try that.

I still have a problem with the whole concept.

What I'm trying to do is publish an HTML list of documents with links to the PDFs. This all gets tossed into a table with a nice JavaScript that allows the table to be filtered on any of the fields (title, model, part number, date...) just like an Excel AutoFilter.

Then I want to direct our Web admins to this HTML page to find out which documents should be published to our Website for that week. If the date field has the actual date, they would have to select each of the 7 days of the week. Unfortunately, they publish on Wednesday so they have to look at two different week numbers to capture all of the documents (the date field in the database automatically pulls the current date when the record is added). I've now tried to mitigate that issue by adding an additional field for us to manually enter the publication week number to the database as a plain number. This gave me more flexibility to post date the publication week and allowed me to have additional "codes" in the publication week number field. But now we face human error issues...

One other problem is that most people do no know what the current week number is. Easy to present this in the database, but my other thread here is about being able to present this in the HTML page for the Web admins...

[Paul Komski]

See how you get on and post back. I found date and date-manipulation to regularly be the most trying problem not only with VB/VBA but databases in general. Again and again it would take some specially defined function to get things the way one wanted.

Using customised functions that call other customised functions can often be a very good way to work around these "problems". In other words try to keep the code as simple as possible and then call it as needed. Better to use three simple functions than one complicated one.

[Cuc Tu]

I worked out my date issue. I'm just going with the date and sorting the HTML table.

Now I'm trying to help my users by flagging a duplicate entry when it happens in the Form field, before they enter all the other data...

I found this nice code that should work, but I get a compile error on execute:

I'm trying to prevent duplicate entries on the "Part Number" field in the "Document Table" table.



Private Sub Part_Number_BeforeUpdate(Cancel As Integer)

'*********************************
'Code sample courtesy of srfreeman
'*********************************

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.strPart_Number.Value
stLinkCriteria = "[strPart_Number]=" & "'" & SID & "'"

'Check StudentDetails table for duplicate StudentNumber
If DCount("strPart_Number", "tblDocument_Table", _
stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning Student Number " _
& SID & " has already been entered." _
& vbCr & vbCr & "You will now been taken to the record.", _
vbInformation, "Duplicate Information"
'Go to record of original Student Number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing
End Sub



I get a compile error here:

SID = Me.strPart_Number.Value

[Paul Komski]

A simple observation is the use of Me.Undo in a BeforeUpdate Event; there can be nothing to undo until a record has been updated.

I would either set the field in question to not allow duplicates in the underlying Table and allow the system to generate a rejection and system message or allow duplicates but check for an existing entry using a function such as:

Function DuplicationCheck(stValue As String, stTableName As String, stFieldName As String) As Boolean
DuplicationCheck = False
Dim MyDb As Database, MyTable As Recordset
Set MyDb = DBEngine.Workspaces(0).Databases(0)
Set MyTable = MyDb.OpenRecordset(stTableName, DB_OPEN_DYNASET)
MyTable.MoveFirst
Do Until MyTable.EOF 'loop through till find existing entry
If MyTable(stFieldName) = stValue Then
DuplicationCheck = True
Exit Do
End If
MyTable.MoveNext
Loop
MyTable.Close
Set MyDb = Nothing
End Function

You would then use something like the following in your code

Private Sub CheckForError ()
Dim SID As String, stTableName As String, stPartNum as String
SID = Me.strPart_Number 'NB This Field would have to have been updated
'or else use a temporary Unbound TextBox or similar
'and then use that value as necessary
stTableName = "Document Table"
stPartNum = "Part Number"
If DuplicationCheck(SID, stTableName, stPartNum) Then
'Do Stuff Here; for example
MsgBox "Entry already exists."
End If
End Sub

PS I still advise not using names with spaces in them - but that's up to you. In practice I hardly ever use BeforeUpdate events preferring to use AfterUpdate and then make corrections as necessary with .undo or .oldvalue type values.

[Cuc Tu]

Currently, the field is set to not allow duplicates, but users get a little upset after filling out all the info to have it rejected at the end of the process.


It's puzzling because the sample database actually works and I really like this solution because it automatically takes the user to the existing record for update.

The sample database comes from here:

http://www.databasedev.co.uk/downloads.html

and the direct link to a zip to see it working:

http://www.databasedev.co.uk/downloads/Prevent_Duplicates_2000.zip

[Paul Komski]

OK then: starting with basics, if it won't compile, then when you choose to debug, what line of code does it take you to?

[Cuc Tu]

I figured it out! My field and table names did not match. I thought str and tbl had meaning in the code, such as string and table...I just took those out and it works wonderfully...

I also took your advice and removed spaces from all my table, form, and field names.

Now, how to modify that code so it works on two fields? I actually have a PartNumberSeries field and a PartNumberEnd field. These are both set as primary keys so the combination is used to check for duplicates.

What happens now is that the user is told they have a duplicate when they really don't, or they are taken to the wrong part number series.

The part numbers are set up as Series-Number, like:

10000-00001
10000-00002
20000-00001
20000-00002
etc...

The series must be selected from a combo box and uses a lookup in the table.

The end part number is typed in.

Later on I will add a 3rd field that houses an ISO language code:

10000-00001-en
10000-00001-de

Also, I have a button on the form that writes out a text file. The problem is that when this is executed, the data on the form has not yet beed added to the table. I was looking at how to update the table at the start of the code. This is probably more important now.

[Cuc Tu]

I think this is an example of what I need:



if DCount("*", "ClassesTaken", _
"PupilID = " & me.tbPupilID & " " & _
"AND ClassID = " & me.tbClassID & " " & _
"AND TermID = " & me.tbTermID) > 0 Then
'a record already exists
msgbox("this pupil is already registered for this class for this term.")
else
'data is unique, do the insert
currentdb.execute "INSERT INTO ... "
end if



I don't know how to properly complete the currentdb.execute command.

[Cuc Tu]

OK, one part solved, I added:

DoCmd.RunCommand acCmdSaveRecord

right after the function header to update the table.

[Cuc Tu]

I get missing operator on the If DCount line



Private Sub PartNumber_BeforeUpdate(Cancel As Integer)

'add series
Dim PartNumberSeries As String
Dim PartNumber As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

'add series
PartNumberSeries = Me.PartNumberSeries.Value

PartNumber = Me.PartNumber.Value

stLinkCriteria = "[PartNumberSeries]=" & "'" & PartNumberSeries & "'" & "[PartNumber]=" & "'" & PartNumber & "'"

'Check DocumentTable for duplicate PartNumber

'If DCount("PartNumberSeries = " & Me.PartNumberSeries & " & "AND PartNumber = " & Me.PartNumber") "DocumentTable") > 0 Then
'If DCount("*", "DocumentTable", "PartNumber" & "PartNumberSeries", stLinkCriteria) > 0 Then

'End If

If DCount("[PartNumberSeries]" & "[PartNumber]", "DocumentTable", stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning! Part Number " _
& PartNumber & " has already been entered." _
& vbCr & vbCr & "You will now been taken to the record.", _
vbInformation, "Duplicate Information"
'Go to record of original Student Number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing
End Sub



And then I have to figure our how to go to the correct record.

[Paul Komski]

I get missing operator on the If DCount line

Looks like a syntactical problem (no quotes should wrap the ampersand)

If DCount("[PartNumberSeries]" & "[PartNumber]", "DocumentTable", stLinkCriteria) > 0 Then
versus the removal of two "
If DCount("[PartNumberSeries] & [PartNumber]", "DocumentTable", stLinkCriteria) > 0 Then
or + in place of the &
If DCount("[PartNumberSeries] + [PartNumber]", "DocumentTable", stLinkCriteria) > 0 Then

If expr identifies multiple fields, separate the field names with a concatenation operator, either an ampersand (&) or the addition operator (+). If you use an ampersand to separate the fields, the DCount function returns the number of records containing data in any of the listed fields. If you use the addition operator, the DCount function returns only the number of records containing data in all of the listed fields. The following example demonstrates the effects of each operator when used with a field that contains data in all records (ShipName) and a field that contains no data (ShipRegion).

intW = DCount("[ShipName]", "Orders") ' Returns 831.
intX = DCount("[ShipRegion]", "Orders") ' Returns 323.
intY = DCount("[ShipName] + [ShipRegion]", "Orders") ' Returns 323.
intZ = DCount("[ShipName] & [ShipRegion]", "Orders") ' Returns 831.

Note The ampersand is the preferred operator for performing string concatenation. You should avoid using the addition operator for anything other than numeric addition, unless you specifically wish to propagate Nulls through an expression.

[Cuc Tu]

I tried that too, but I still get a missing operator:

Syntax error (missing operator) in query expression '[PartNumberSeries]='11111'[PartNumber]='00001"


I scrapped this idea as the second part of finding the correct record seems even harder, but not a total loss...I decided to concatenate the series and number fields in a new field on the table for the record, then I ran the code above on that one new field. It also finds the correct record.

It works like this:



Private Sub PartNumberEnd_BeforeUpdate(Cancel As Integer)

Dim tmp15
tmp15 = [PartNumberSeries].Value


If (tmp15 = "") Or IsNull(tmp15) Then
MsgBox "Please First enter a valid Part Number Series", vbOKOnly, "PartNumberSeries Error"
Me.Undo
GoTo ValidErr

Else

Me.PartNumber = (Me.PartNumberSeries & "-" & Me.PartNumberEnd)
Call CheckDuplicate
End If
ValidErr:
End Sub

-------------------------

Private Function CheckDuplicate()

'add series
'Dim PartNumberSeries As String
'Dim PartNumberEnd As String
Dim PartNumber As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset



Set rsc = Me.RecordsetClone


PartNumber = Me.PartNumber.Value
stLinkCriteria = "[PartNumber]=" & "'" & PartNumber & "'"
'Check DocumentTable for duplicate PartNumber
If DCount("PartNumber", "DocumentTable", stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning! Part Number " _
& PartNumber & " has already been entered." _
& vbCr & vbCr & "You will now be taken to the existing record." _
& vbCr & vbCr & "Please modify the record accordingly.", _
vbInformation, "Duplicate Information"
'Go to record of original Student Number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing
End Function



Notice I put a check for the part number series before concatenation and cleared the form if this field was skipped.