Replies

Oh boy! The only thing that may save this is that there are infrequent updates to the data, only by 2 or 3 people. The database will be used mostly by users searching the data and the number of records is unlikely to exceed 2000 records for the next decade.

Would an application spread over three network drives be a good candidate for SQL server?

I really like Access, but the conversion to new versions is really a pain.

I read some of the articles on replication on trigeminal.com.

My application is to use an Access97 backend for the data (all the tables) and Access 97, 2000, & XP frontends.

I plan on using a button on the startup form to replicate, two way replication, the Master and two replicas.

Will I have problems in replication with this scenario?

Thank you for your response. I have bookmarked the page and will check it out.
The problem was that I had typos in the address.

Yes, that works. All I needed was another “” in the first address. I also found an error in the second address, the file extension was “md” instead of “mdb.

I tested it and it worked!

Thank you very much.

Thank you once again Hans. I had forgotton that I had put that invisible text box in the header.

I tried another approach. I created buttons for each Skill field and put SQL code behind it.
Each Skill field is a Checkbox. I repeated the code for each skill field.
For example for Asbestos Removal.

Private Sub cmdAsbestosRemoval_Click()

Dim strSQL As String

strSQL = “SELECT * FROM qryEngineersReview WHERE qryEngineersReview.AsbestosRemoval = TRUE”
Me.Form.RecordSource = strSQL

End Sub

Then I created a button to clear the filter.
Private Sub cmdRemoveSkillFilter_Click()

Dim strSQL As String

strSQL = “SELECT * FROM qryEngineersReview”
Me.Form.RecordSource = strSQL

End Sub

Thank you for your input.

Sorry for the slow response, but I have been working on some other things.

The Skill boxes denote if a Contractor has that skill. I want for users to be able to filter with buttons for the skill that they are interested in.

I will try to implement your suggestions and get back with my solution.

This application will be used by many users outside the department, thus a button(s) would be useful.
I had a problem with a user the other day that did not know how to copy a file and they are a long time computer user.
Trying to teach many users would be burdensome and ongoing as personnel are changing frequently.

Thus I would like to do it with VBA.

Yahoo! That did the trick. Thank you very much. I was looking in the wrong place.

Thank you, here is the zipped file.

I inserted the lines as instructed and I still get #Error.

Function GetWorkDays(ByVal StartDate As Variant, _
ByVal EndDate As Variant, _
ByRef CountHolidays As Boolean) As Long

Dim lngWeeks As Long
Dim tmpDate As Date
Dim intDays As Integer

If IsNull(StartDate) Or IsNull(EndDate) Then
GetWorkDays = 0
Exit Function
End If

lngWeeks = DateDiff(“w”, StartDate, EndDate)

Thank you for your response. I inserted the code as follows. I am still receiving the #Error when StartDate or EndDate do not have entries in the table.
I tried inserting the code in different places. It is interesting that the code you used is identical to some code I tried earlier except for the line, EXIT FUNCTION.

Function GetWorkDays(ByVal StartDate As Variant, _
ByVal EndDate As Variant, _
ByRef CountHolidays As Boolean) As Long

Dim lngWeeks As Long
Dim tmpDate As Date
Dim intDays As Integer

lngWeeks = DateDiff(“w”, StartDate, EndDate)
tmpDate = DateAdd(“ww”, lngWeeks, StartDate)
intDays = 0

If IsNull(StartDate) Or IsNull(EndDate) Then
GetWorkDays = 0
Exit Function
End If

Do While tmpDate <= EndDate
'The code continues, see orginal posting for the whole code.

Hans, I have declared the values as Variants and have tried many permutations of code to get this function to return zero (0) days when either or both the StartDate & EndDate are missing.
I need your help as I have been banging my head for some time. I also tried setting all variables to Variant type. I have tried IsNull() and IsDate() with If statements.

Function GetWorkDays(ByVal StartDate As Variant, _
ByVal EndDate As Variant, _
ByRef CountHolidays As Boolean) As Long

Dim lngWeeks As Long
Dim tmpDate As Date
Dim intDays As Integer

lngWeeks = DateDiff(“w”, StartDate, EndDate)
tmpDate = DateAdd(“ww”, lngWeeks, StartDate)
intDays = 0

Do While tmpDate <= EndDate
If Weekday(tmpDate) vbSunday And Weekday(tmpDate) vbSaturday Then
intDays = intDays + 1
End If
tmpDate = DateAdd(“d”, 1, tmpDate)
Loop

If CountHolidays = True Then
GetWorkDays = lngWeeks * 5 + intDays – GetHolidayCount(StartDate, EndDate)
Else
GetWorkDays = lngWeeks * 5 + intDays
End If

End Function

I inserted the following line after (see code in start of thread).

‘Find the first matching record.
TempRecSet.FindFirst LookFor
DoCmd.RunCommand acCmdRecordsGoToFirst ‘This is the line that I inserted.

And it works great! Thank you once again Hans.

I will try it and get back to you later.