Duplicate Records Question

[steve goodrich]

I have an Access 97 database that is used on the network by 110 members of
staff to book visitors into our building.

The fields are

Date of Access, Name, Company etc

I have a duplicate button on my form, so if the same visitor is expected on
more than one occasion the user enters the visitor on the form, hits
duplicate, then just alters the date.

This has worked great for years.

Recently, we have had visitors coming in all week and some for a month at a
time. So it is quite time consuming entering all the information by hitting
a duplicate button, especially if you get a dozen visitors all coming for a
month.



What's the best way of doing this?



Here's what I've tried so far.



I created a second date field "date to" and amended the date of access field
to "date from". I have had all sorts of problems with queries (most of them
resolved by you guys) but at the end of the day it isn't exactly what I
want.

Using two date fields only creates one record where as using the duplicate
button created one record per day. This works far better for our needs.



My question is this



Is it possible to put a button on my form which will, after completing the
form once, book the visitor in all week or month or any number of days when
prompted and create a separate record for each day?



Hope all this make sense



Any help would be greatly appreciated



Steve

 

[Douglas J. Steele]

You could use VBA code to generate the multiple entries.

Dim dtmCurr As Date
Dim strSQL As String

For dtmCurr = Me.txtDateFrom To Me.txtDateTo
strSQL = "INSERT INTO MyTable (DateOfAccess, [Name], Company...) " & _
"VALUES(" & Format(dtmCurr, "\#mm\/dd\/yyyy\#") & ", " & _
Chr$(34) & Me.txtName & Chr$(34) & ", " & _
Chr$(34) & Me.txtCompany & Chr$(34) ... & ")"
CurrentDb.Execute strSQL, dbFailOnError
Next dtmCurr

However, I think it would be better to have a single row in the table,
rather than multiple. What sort of problems are you running into?

 

[steve goodrich]

The main problems I've been having are with criteria using 2 date fields in
the same table. With the help of this group most have been resolved. The
ones that are used every day are listed below and all seem to be working ok



DAILY LIST

"DATE FROM" "DATE TO"

CRITERIA <=DATE() >=DATE()



TOMORROWS LIST

"DATE FROM" "DATE TO"

CRITERIA <=DATE()+1 >=DATE()+1



The "List by date" parameter query below I'm told will give results but may
not be correct - I've tested it myself with different combinations and it
seems to work ok. I'm only prompted with the "date from" box then the query
runs



LIST BY DATE

"DATE FROM" "DATE TO"

CRITERIA <=[ENTER DATE] >=[ENTER DATE]



The query I can't get to run is a parameter query for a date range. I want
to be prompted for a "date from" date then a "date to" date so I can list
all dates within that range. E.g. 1/1/07 to 7/1/07



I thought the criteria from the list by date query would do it but I was
wrong

I tried typing the dates in the query itself like this



Date From Date To

<=1/1/07 >=7/1/07



The dates were present in the query only if the start or end date happened
to be 1/7/07 or 7/1/07

If a record had been entered like this - date from 2/1/07 to 8/1/07 then it
wouldn't show up



Any suggestions welcomed.



Also it was awkward to delete specific days unlike the one record per day
where the user would just delete the record for that day. So if a visitor is
no longer coming on say the 1st 7th & 20th and he was booked in for a month
you would have to delete the whole month and make 3 new entries for 2nd to
6th 8th to 19th & 21st to 31st



Any help you could give would be appreciated



Steve

You could use VBA code to generate the multiple entries.

Dim dtmCurr As Date
Dim strSQL As String

For dtmCurr = Me.txtDateFrom To Me.txtDateTo
strSQL = "INSERT INTO MyTable (DateOfAccess, [Name], Company...) " & _
"VALUES(" & Format(dtmCurr, "\#mm\/dd\/yyyy\#") & ", " & _
Chr$(34) & Me.txtName & Chr$(34) & ", " & _
Chr$(34) & Me.txtCompany & Chr$(34) ... & ")"
CurrentDb.Execute strSQL, dbFailOnError
Next dtmCurr

However, I think it would be better to have a single row in the table,
rather than multiple. What sort of problems are you running into?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have an Access 97 database that is used on the network by 110 members of
staff to book visitors into our building.

The fields are

Date of Access, Name, Company etc

I have a duplicate button on my form, so if the same visitor is expected
on more than one occasion the user enters the visitor on the form, hits
duplicate, then just alters the date.

This has worked great for years.

Recently, we have had visitors coming in all week and some for a month at
a time. So it is quite time consuming entering all the information by
hitting a duplicate button, especially if you get a dozen visitors all
coming for a month.



What's the best way of doing this?



Here's what I've tried so far.



I created a second date field "date to" and amended the date of access
field to "date from". I have had all sorts of problems with queries (most
of them resolved by you guys) but at the end of the day it isn't exactly
what I want.

Using two date fields only creates one record where as using the
duplicate button created one record per day. This works far better for
our needs.



My question is this



Is it possible to put a button on my form which will, after completing
the form once, book the visitor in all week or month or any number of
days when prompted and create a separate record for each day?



Hope all this make sense



Any help would be greatly appreciated



Steve

 

[Douglas J. Steele]

From your sample data, it would appear that you're trying to use dates in
dd/mm/yyyy format.

Regardless of what your Short Date format has been set to through Regional
Settings, you must use mm/dd/yyyy format.

You may find it useful to read Allen Browne's "International Dates in
Access" at http://allenbrowne.com/ser-36.html or what I have in my September
2003 Access Answers column for Pinnacle Publication's "Smart Access"
newsletter. (The column and accompanying database can be downloaded at
http://www.accessmvp.com/djsteele/SmartAccess.html)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The main problems I've been having are with criteria using 2 date fields
in the same table. With the help of this group most have been resolved.
The ones that are used every day are listed below and all seem to be
working ok



DAILY LIST

"DATE FROM" "DATE TO"

CRITERIA <=DATE() >=DATE()



TOMORROWS LIST

"DATE FROM" "DATE TO"

CRITERIA <=DATE()+1 >=DATE()+1



The "List by date" parameter query below I'm told will give results but
may not be correct - I've tested it myself with different combinations and
it seems to work ok. I'm only prompted with the "date from" box then the
query runs



LIST BY DATE

"DATE FROM" "DATE TO"

CRITERIA <=[ENTER DATE] >=[ENTER DATE]



The query I can't get to run is a parameter query for a date range. I
want to be prompted for a "date from" date then a "date to" date so I can
list all dates within that range. E.g. 1/1/07 to 7/1/07



I thought the criteria from the list by date query would do it but I was
wrong

I tried typing the dates in the query itself like this



Date From Date To

<=1/1/07 >=7/1/07



The dates were present in the query only if the start or end date happened
to be 1/7/07 or 7/1/07

If a record had been entered like this - date from 2/1/07 to 8/1/07 then
it wouldn't show up



Any suggestions welcomed.



Also it was awkward to delete specific days unlike the one record per day
where the user would just delete the record for that day. So if a visitor
is no longer coming on say the 1st 7th & 20th and he was booked in for a
month you would have to delete the whole month and make 3 new entries for
2nd to 6th 8th to 19th & 21st to 31st



Any help you could give would be appreciated



Steve

You could use VBA code to generate the multiple entries.

Dim dtmCurr As Date
Dim strSQL As String

For dtmCurr = Me.txtDateFrom To Me.txtDateTo
strSQL = "INSERT INTO MyTable (DateOfAccess, [Name], Company...) " & _
"VALUES(" & Format(dtmCurr, "\#mm\/dd\/yyyy\#") & ", " & _
Chr$(34) & Me.txtName & Chr$(34) & ", " & _
Chr$(34) & Me.txtCompany & Chr$(34) ... & ")"
CurrentDb.Execute strSQL, dbFailOnError
Next dtmCurr

However, I think it would be better to have a single row in the table,
rather than multiple. What sort of problems are you running into?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have an Access 97 database that is used on the network by 110 members
of staff to book visitors into our building.

The fields are

Date of Access, Name, Company etc

I have a duplicate button on my form, so if the same visitor is expected
on more than one occasion the user enters the visitor on the form, hits
duplicate, then just alters the date.

This has worked great for years.

Recently, we have had visitors coming in all week and some for a month
at a time. So it is quite time consuming entering all the information by
hitting a duplicate button, especially if you get a dozen visitors all
coming for a month.



What's the best way of doing this?



Here's what I've tried so far.



I created a second date field "date to" and amended the date of access
field to "date from". I have had all sorts of problems with queries
(most of them resolved by you guys) but at the end of the day it isn't
exactly what I want.

Using two date fields only creates one record where as using the
duplicate button created one record per day. This works far better for
our needs.



My question is this



Is it possible to put a button on my form which will, after completing
the form once, book the visitor in all week or month or any number of
days when prompted and create a separate record for each day?



Hope all this make sense



Any help would be greatly appreciated



Steve

 

[steve goodrich]

Thanks Doug, I'll take a look

From your sample data, it would appear that you're trying to use dates in
dd/mm/yyyy format.

Regardless of what your Short Date format has been set to through Regional
Settings, you must use mm/dd/yyyy format.

You may find it useful to read Allen Browne's "International Dates in
Access" at http://allenbrowne.com/ser-36.html or what I have in my
September 2003 Access Answers column for Pinnacle Publication's "Smart
Access" newsletter. (The column and accompanying database can be
downloaded at http://www.accessmvp.com/djsteele/SmartAccess.html)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The main problems I've been having are with criteria using 2 date fields
in the same table. With the help of this group most have been resolved.
The ones that are used every day are listed below and all seem to be
working ok



DAILY LIST

"DATE FROM" "DATE TO"

CRITERIA <=DATE() >=DATE()



TOMORROWS LIST

"DATE FROM" "DATE TO"

CRITERIA <=DATE()+1 >=DATE()+1



The "List by date" parameter query below I'm told will give results but
may not be correct - I've tested it myself with different combinations
and it seems to work ok. I'm only prompted with the "date from" box then
the query runs



LIST BY DATE

"DATE FROM" "DATE TO"

CRITERIA <=[ENTER DATE] >=[ENTER DATE]



The query I can't get to run is a parameter query for a date range. I
want to be prompted for a "date from" date then a "date to" date so I can
list all dates within that range. E.g. 1/1/07 to 7/1/07



I thought the criteria from the list by date query would do it but I was
wrong

I tried typing the dates in the query itself like this



Date From Date To

<=1/1/07 >=7/1/07



The dates were present in the query only if the start or end date
happened to be 1/7/07 or 7/1/07

If a record had been entered like this - date from 2/1/07 to 8/1/07 then
it wouldn't show up



Any suggestions welcomed.



Also it was awkward to delete specific days unlike the one record per day
where the user would just delete the record for that day. So if a visitor
is no longer coming on say the 1st 7th & 20th and he was booked in for a
month you would have to delete the whole month and make 3 new entries for
2nd to 6th 8th to 19th & 21st to 31st



Any help you could give would be appreciated



Steve

You could use VBA code to generate the multiple entries.

Dim dtmCurr As Date
Dim strSQL As String

For dtmCurr = Me.txtDateFrom To Me.txtDateTo
strSQL = "INSERT INTO MyTable (DateOfAccess, [Name], Company...) " &
_
"VALUES(" & Format(dtmCurr, "\#mm\/dd\/yyyy\#") & ", " & _
Chr$(34) & Me.txtName & Chr$(34) & ", " & _
Chr$(34) & Me.txtCompany & Chr$(34) ... & ")"
CurrentDb.Execute strSQL, dbFailOnError
Next dtmCurr

However, I think it would be better to have a single row in the table,
rather than multiple. What sort of problems are you running into?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have an Access 97 database that is used on the network by 110 members
of staff to book visitors into our building.

The fields are

Date of Access, Name, Company etc

I have a duplicate button on my form, so if the same visitor is
expected on more than one occasion the user enters the visitor on the
form, hits duplicate, then just alters the date.

This has worked great for years.

Recently, we have had visitors coming in all week and some for a month
at a time. So it is quite time consuming entering all the information
by hitting a duplicate button, especially if you get a dozen visitors
all coming for a month.



What's the best way of doing this?



Here's what I've tried so far.



I created a second date field "date to" and amended the date of access
field to "date from". I have had all sorts of problems with queries
(most of them resolved by you guys) but at the end of the day it isn't
exactly what I want.

Using two date fields only creates one record where as using the
duplicate button created one record per day. This works far better for
our needs.



My question is this



Is it possible to put a button on my form which will, after completing
the form once, book the visitor in all week or month or any number of
days when prompted and create a separate record for each day?



Hope all this make sense



Any help would be greatly appreciated



Steve