Creating multiple database entries from a date range

[Fred]

Hello,
I have created a form where I need to reserve visitor parking and on
the form I have Requested Date and Depature date. All days in between
should be reserved for parking but at this time it is only the
requested date. I need help with the code that needs to be written to
first figure out the dates and then put a single entry in the table for
each day that is requested with all the other information that was
input on the form. Thanx for your help Fred

 

[Jeff L]

Try this:

Dim DaysBetween as Integer, I as Integer

DaysBetween = DateDiff("d", Me.Requested, Me.DepartureDate)

For I = 0 to DaysBetween

Docmd.RunSql "Insert into YourTableName(Field1, Field2) " & _
"Values(" Me.VisitorId & ", '" & Me.RequestedDate + I & "');"

Next I

Hope that helps!

 

[Fred]

Where do I put this script? Do I create a macro? I am not sure where to
put it. Thanx Fred

 

[Jeff L]

Probably the best place would be to put it in the On Click event of a
Command Button. Use the button to make your parking reservation.

 

[Fred]

I put the code in the Submit command button but the sql script is
giving me a compile end of statement error. I have a LastName instead
of a visitor id. It is highlighting the first Me. I really appreciate
the help Fred

 

[Jeff L]

When you use a string value like Last Name, you need to put single
quotes around it. So it's Values('" & Me.LastName & "',"

Values([SingleQuote][DoubleQuote] & Me.YourFieldName &
[DoubleQuote][SingleQuote],[DoubleQuote]

When using numeric values, the single quotes are not needed.

If you are still having trouble, maybe you could post your code and let
me look at it.

 

[Fred]

Okay I still am fighting this so here is the code I am using

Private Function DaysBetween()


Dim DaysBetween As Integer, I As Integer


DaysBetween = DateDiff("d", Me.DateRequested, Me.DepartureDate)


For I = 0 To DaysBetween

DoCmd.RunSQL "Insert Suffolk Visitor Parking Log" & _
"Values(""& Me.Lastname & "", '" & Me.DateRequested + I & "');"

Next I


End Function
Again thanx for all the help

When you use a string value like Last Name, you need to put single
quotes around it. So it's Values('" & Me.LastName & "',"

Values([SingleQuote][DoubleQuote] & Me.YourFieldName &
[DoubleQuote][SingleQuote],[DoubleQuote]

When using numeric values, the single quotes are not needed.

If you are still having trouble, maybe you could post your code and let
me look at it.

I put the code in the Submit command button but the sql script is
giving me a compile end of statement error. I have a LastName instead
of a visitor id. It is highlighting the first Me. I really appreciate
the help Fred

 

[Jeff L]

What you have:
DoCmd.RunSQL "Insert Suffolk Visitor Parking Log" & _
"Values(""& Me.Lastname & "", '" & Me.DateRequested + I & "');"

What it should be:
DoCmd.RunSQL "Insert Into [Suffolk Visitor Parking Log] " & _
"Values('"& Me.Lastname & "', '" & Me.DateRequested + I & "');"

Just curious, but how are you going to distinguish between people with
the same last name? You should use a unique identifier, like
VisitorID, to distinguish who your visitors are.

Okay I still am fighting this so here is the code I am using

Private Function DaysBetween()


Dim DaysBetween As Integer, I As Integer


DaysBetween = DateDiff("d", Me.DateRequested, Me.DepartureDate)


For I = 0 To DaysBetween

DoCmd.RunSQL "Insert Suffolk Visitor Parking Log" & _
"Values(""& Me.Lastname & "", '" & Me.DateRequested + I & "');"

Next I


End Function
Again thanx for all the help

When you use a string value like Last Name, you need to put single
quotes around it. So it's Values('" & Me.LastName & "',"

Values([SingleQuote][DoubleQuote] & Me.YourFieldName &
[DoubleQuote][SingleQuote],[DoubleQuote]

When using numeric values, the single quotes are not needed.

If you are still having trouble, maybe you could post your code and let
me look at it.

I put the code in the Submit command button but the sql script is
giving me a compile end of statement error. I have a LastName instead
of a visitor id. It is highlighting the first Me. I really appreciate
the help Fred
Jeff L wrote:
Probably the best place would be to put it in the On Click event of a
Command Button. Use the button to make your parking reservation.

 

[Fred]

It is still not working and I have already made that suggestion to the
owner of the database. Here is what I have. Thanx for all your help

Private Sub DaysBetween_Click()


Dim DaysBetween As Integer, I As Integer


DaysBetween = DateDiff("d", Me.DateRequested, Me.DepartureDate)


For I = 0 To DaysBetween

DoCmd.RunSQL "Insert Into [Suffolk Visitor Parking Log] " & _
"Values('" & Me.Lastname & "', '" & Me.DateRequested + I & "');"



Next I

End Sub

What you have:
DoCmd.RunSQL "Insert Suffolk Visitor Parking Log" & _
"Values(""& Me.Lastname & "", '" & Me.DateRequested + I & "');"

What it should be:
DoCmd.RunSQL "Insert Into [Suffolk Visitor Parking Log] " & _
"Values('"& Me.Lastname & "', '" & Me.DateRequested + I & "');"

Just curious, but how are you going to distinguish between people with
the same last name? You should use a unique identifier, like
VisitorID, to distinguish who your visitors are.

Okay I still am fighting this so here is the code I am using

Private Function DaysBetween()


Dim DaysBetween As Integer, I As Integer


DaysBetween = DateDiff("d", Me.DateRequested, Me.DepartureDate)


For I = 0 To DaysBetween

DoCmd.RunSQL "Insert Suffolk Visitor Parking Log" & _
"Values(""& Me.Lastname & "", '" & Me.DateRequested + I & "');"

Next I


End Function
Again thanx for all the help

When you use a string value like Last Name, you need to put single
quotes around it. So it's Values('" & Me.LastName & "',"

Values([SingleQuote][DoubleQuote] & Me.YourFieldName &
[DoubleQuote][SingleQuote],[DoubleQuote]

When using numeric values, the single quotes are not needed.

If you are still having trouble, maybe you could post your code and let
me look at it.


Fred wrote:
I put the code in the Submit command button but the sql script is
giving me a compile end of statement error. I have a LastName instead
of a visitor id. It is highlighting the first Me. I really appreciate
the help Fred
Jeff L wrote:
Probably the best place would be to put it in the On Click event of a
Command Button. Use the button to make your parking reservation.

 

[Jeff L]

Ok, you say it's not working. What's not working about it? Are you
getting error messages? Can you tell me what the Primary Key for your
table, [Suffolk Visitor Parking Log], is?

It is still not working and I have already made that suggestion to the
owner of the database. Here is what I have. Thanx for all your help

Private Sub DaysBetween_Click()


Dim DaysBetween As Integer, I As Integer


DaysBetween = DateDiff("d", Me.DateRequested, Me.DepartureDate)


For I = 0 To DaysBetween

DoCmd.RunSQL "Insert Into [Suffolk Visitor Parking Log] " & _
"Values('" & Me.Lastname & "', '" & Me.DateRequested + I & "');"



Next I

End Sub

What you have:
DoCmd.RunSQL "Insert Suffolk Visitor Parking Log" & _
"Values(""& Me.Lastname & "", '" & Me.DateRequested + I & "');"

What it should be:
DoCmd.RunSQL "Insert Into [Suffolk Visitor Parking Log] " & _
"Values('"& Me.Lastname & "', '" & Me.DateRequested + I & "');"

Just curious, but how are you going to distinguish between people with
the same last name? You should use a unique identifier, like
VisitorID, to distinguish who your visitors are.

Okay I still am fighting this so here is the code I am using

Private Function DaysBetween()


Dim DaysBetween As Integer, I As Integer


DaysBetween = DateDiff("d", Me.DateRequested, Me.DepartureDate)


For I = 0 To DaysBetween

DoCmd.RunSQL "Insert Suffolk Visitor Parking Log" & _
"Values(""& Me.Lastname & "", '" & Me.DateRequested + I & "');"

Next I


End Function
Again thanx for all the help
Jeff L wrote:
When you use a string value like Last Name, you need to put single
quotes around it. So it's Values('" & Me.LastName & "',"

Values([SingleQuote][DoubleQuote] & Me.YourFieldName &
[DoubleQuote][SingleQuote],[DoubleQuote]

When using numeric values, the single quotes are not needed.

If you are still having trouble, maybe you could post your code and let
me look at it.


Fred wrote:
I put the code in the Submit command button but the sql script is
giving me a compile end of statement error. I have a LastName instead
of a visitor id. It is highlighting the first Me. I really appreciate
the help Fred
Jeff L wrote:
Probably the best place would be to put it in the On Click event of a
Command Button. Use the button to make your parking reservation.

 

[Duane Hookom]

There is a sample mdb or "recurring" events scheduling at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.


--
Duane Hookom
MS Access MVP

Ok, you say it's not working. What's not working about it? Are you
getting error messages? Can you tell me what the Primary Key for your
table, [Suffolk Visitor Parking Log], is?

It is still not working and I have already made that suggestion to the
owner of the database. Here is what I have. Thanx for all your help

Private Sub DaysBetween_Click()


Dim DaysBetween As Integer, I As Integer


DaysBetween = DateDiff("d", Me.DateRequested, Me.DepartureDate)


For I = 0 To DaysBetween

DoCmd.RunSQL "Insert Into [Suffolk Visitor Parking Log] " & _
"Values('" & Me.Lastname & "', '" & Me.DateRequested + I & "');"



Next I

End Sub

What you have:
DoCmd.RunSQL "Insert Suffolk Visitor Parking Log" & _
"Values(""& Me.Lastname & "", '" & Me.DateRequested + I & "');"

What it should be:
DoCmd.RunSQL "Insert Into [Suffolk Visitor Parking Log] " & _
"Values('"& Me.Lastname & "', '" & Me.DateRequested + I & "');"

Just curious, but how are you going to distinguish between people with
the same last name? You should use a unique identifier, like
VisitorID, to distinguish who your visitors are.




Fred wrote:
Okay I still am fighting this so here is the code I am using

Private Function DaysBetween()


Dim DaysBetween As Integer, I As Integer


DaysBetween = DateDiff("d", Me.DateRequested, Me.DepartureDate)


For I = 0 To DaysBetween

DoCmd.RunSQL "Insert Suffolk Visitor Parking Log" & _
"Values(""& Me.Lastname & "", '" & Me.DateRequested + I & "');"

Next I


End Function
Again thanx for all the help
Jeff L wrote:
When you use a string value like Last Name, you need to put single
quotes around it. So it's Values('" & Me.LastName & "',"

Values([SingleQuote][DoubleQuote] & Me.YourFieldName &
[DoubleQuote][SingleQuote],[DoubleQuote]

When using numeric values, the single quotes are not needed.

If you are still having trouble, maybe you could post your code and
let
me look at it.


Fred wrote:
I put the code in the Submit command button but the sql script is
giving me a compile end of statement error. I have a LastName
instead
of a visitor id. It is highlighting the first Me. I really
appreciate
the help Fred
Jeff L wrote:
Probably the best place would be to put it in the On Click
event of a
Command Button. Use the button to make your parking
reservation.

 

[Fred]

It is not putting the multiple entries in the table and the primary key
is now VisitorId. It is still putting the one entry in so if I query on
a date in the in between the dates they need the parking for it will
not show up. Thanx Fred

Ok, you say it's not working. What's not working about it? Are you
getting error messages? Can you tell me what the Primary Key for your
table, [Suffolk Visitor Parking Log], is?

It is still not working and I have already made that suggestion to the
owner of the database. Here is what I have. Thanx for all your help

Private Sub DaysBetween_Click()


Dim DaysBetween As Integer, I As Integer


DaysBetween = DateDiff("d", Me.DateRequested, Me.DepartureDate)


For I = 0 To DaysBetween

DoCmd.RunSQL "Insert Into [Suffolk Visitor Parking Log] " & _
"Values('" & Me.Lastname & "', '" & Me.DateRequested + I & "');"



Next I

End Sub

What you have:
DoCmd.RunSQL "Insert Suffolk Visitor Parking Log" & _
"Values(""& Me.Lastname & "", '" & Me.DateRequested + I & "');"

What it should be:
DoCmd.RunSQL "Insert Into [Suffolk Visitor Parking Log] " & _
"Values('"& Me.Lastname & "', '" & Me.DateRequested + I & "');"

Just curious, but how are you going to distinguish between people with
the same last name? You should use a unique identifier, like
VisitorID, to distinguish who your visitors are.




Fred wrote:
Okay I still am fighting this so here is the code I am using

Private Function DaysBetween()


Dim DaysBetween As Integer, I As Integer


DaysBetween = DateDiff("d", Me.DateRequested, Me.DepartureDate)


For I = 0 To DaysBetween

DoCmd.RunSQL "Insert Suffolk Visitor Parking Log" & _
"Values(""& Me.Lastname & "", '" & Me.DateRequested + I & "');"

Next I


End Function
Again thanx for all the help
Jeff L wrote:
When you use a string value like Last Name, you need to put single
quotes around it. So it's Values('" & Me.LastName & "',"

Values([SingleQuote][DoubleQuote] & Me.YourFieldName &
[DoubleQuote][SingleQuote],[DoubleQuote]

When using numeric values, the single quotes are not needed.

If you are still having trouble, maybe you could post your code and let
me look at it.


Fred wrote:
I put the code in the Submit command button but the sql script is
giving me a compile end of statement error. I have a LastName instead
of a visitor id. It is highlighting the first Me. I really appreciate
the help Fred
Jeff L wrote:
Probably the best place would be to put it in the On Click event of a
Command Button. Use the button to make your parking reservation.

 

[Jeff L]

You cannot insert multiple rows to a table that have the same primary
key. The primary key must be unique. You could make the key
VisitorId, DateRequested. Having it as VisitorId will only work for
the first record that you insert, then when you try to insert the
second record, you will have a duplicate primary key and the insert
will fail.

Hope that helps!

It is not putting the multiple entries in the table and the primary key
is now VisitorId. It is still putting the one entry in so if I query on
a date in the in between the dates they need the parking for it will
not show up. Thanx Fred

Ok, you say it's not working. What's not working about it? Are you
getting error messages? Can you tell me what the Primary Key for your
table, [Suffolk Visitor Parking Log], is?

It is still not working and I have already made that suggestion to the
owner of the database. Here is what I have. Thanx for all your help

Private Sub DaysBetween_Click()


Dim DaysBetween As Integer, I As Integer


DaysBetween = DateDiff("d", Me.DateRequested, Me.DepartureDate)


For I = 0 To DaysBetween

DoCmd.RunSQL "Insert Into [Suffolk Visitor Parking Log] " & _
"Values('" & Me.Lastname & "', '" & Me.DateRequested + I & "');"



Next I

End Sub


Jeff L wrote:
What you have:
DoCmd.RunSQL "Insert Suffolk Visitor Parking Log" & _
"Values(""& Me.Lastname & "", '" & Me.DateRequested + I & "');"

What it should be:
DoCmd.RunSQL "Insert Into [Suffolk Visitor Parking Log] " & _
"Values('"& Me.Lastname & "', '" & Me.DateRequested + I & "');"

Just curious, but how are you going to distinguish between people with
the same last name? You should use a unique identifier, like
VisitorID, to distinguish who your visitors are.




Fred wrote:
Okay I still am fighting this so here is the code I am using

Private Function DaysBetween()


Dim DaysBetween As Integer, I As Integer


DaysBetween = DateDiff("d", Me.DateRequested, Me.DepartureDate)


For I = 0 To DaysBetween

DoCmd.RunSQL "Insert Suffolk Visitor Parking Log" & _
"Values(""& Me.Lastname & "", '" & Me.DateRequested + I & "');"

Next I


End Function
Again thanx for all the help
Jeff L wrote:
When you use a string value like Last Name, you need to put single
quotes around it. So it's Values('" & Me.LastName & "',"

Values([SingleQuote][DoubleQuote] & Me.YourFieldName &
[DoubleQuote][SingleQuote],[DoubleQuote]

When using numeric values, the single quotes are not needed.

If you are still having trouble, maybe you could post your code and let
me look at it.


Fred wrote:
I put the code in the Submit command button but the sql script is
giving me a compile end of statement error. I have a LastName instead
of a visitor id. It is highlighting the first Me. I really appreciate
the help Fred
Jeff L wrote:
Probably the best place would be to put it in the On Click event of a
Command Button. Use the button to make your parking reservation.

 

[Fred]

Will it work if I have no primary key? How would I go about making the
primary key VisitorID and DateRequested? Thanx for your help. Fred

You cannot insert multiple rows to a table that have the same primary
key. The primary key must be unique. You could make the key
VisitorId, DateRequested. Having it as VisitorId will only work for
the first record that you insert, then when you try to insert the
second record, you will have a duplicate primary key and the insert
will fail.

Hope that helps!

It is not putting the multiple entries in the table and the primary key
is now VisitorId. It is still putting the one entry in so if I query on
a date in the in between the dates they need the parking for it will
not show up. Thanx Fred

Ok, you say it's not working. What's not working about it? Are you
getting error messages? Can you tell me what the Primary Key for your
table, [Suffolk Visitor Parking Log], is?


Fred wrote:
It is still not working and I have already made that suggestion to the
owner of the database. Here is what I have. Thanx for all your help

Private Sub DaysBetween_Click()


Dim DaysBetween As Integer, I As Integer


DaysBetween = DateDiff("d", Me.DateRequested, Me.DepartureDate)


For I = 0 To DaysBetween

DoCmd.RunSQL "Insert Into [Suffolk Visitor Parking Log] " & _
"Values('" & Me.Lastname & "', '" & Me.DateRequested + I & "');"



Next I

End Sub


Jeff L wrote:
What you have:
DoCmd.RunSQL "Insert Suffolk Visitor Parking Log" & _
"Values(""& Me.Lastname & "", '" & Me.DateRequested + I & "');"

What it should be:
DoCmd.RunSQL "Insert Into [Suffolk Visitor Parking Log] " & _
"Values('"& Me.Lastname & "', '" & Me.DateRequested + I & "');"

Just curious, but how are you going to distinguish between people with
the same last name? You should use a unique identifier, like
VisitorID, to distinguish who your visitors are.




Fred wrote:
Okay I still am fighting this so here is the code I am using

Private Function DaysBetween()


Dim DaysBetween As Integer, I As Integer


DaysBetween = DateDiff("d", Me.DateRequested, Me.DepartureDate)


For I = 0 To DaysBetween

DoCmd.RunSQL "Insert Suffolk Visitor Parking Log" & _
"Values(""& Me.Lastname & "", '" & Me.DateRequested + I & "');"

Next I


End Function
Again thanx for all the help
Jeff L wrote:
When you use a string value like Last Name, you need to put single
quotes around it. So it's Values('" & Me.LastName & "',"

Values([SingleQuote][DoubleQuote] & Me.YourFieldName &
[DoubleQuote][SingleQuote],[DoubleQuote]

When using numeric values, the single quotes are not needed.

If you are still having trouble, maybe you could post your code and let
me look at it.


Fred wrote:
I put the code in the Submit command button but the sql script is
giving me a compile end of statement error. I have a LastName instead
of a visitor id. It is highlighting the first Me. I really appreciate
the help Fred
Jeff L wrote:
Probably the best place would be to put it in the On Click event of a
Command Button. Use the button to make your parking reservation.

 

[Fred]

I have figured out the multilpe keys but still no luck I am posting the
script again in case I have messed it up. Thanx Fred

Private Sub DaysBetween_Click()


Dim DaysBetween As Integer, I As Integer


DaysBetween = DateDiff("d", Me.DateRequested, Me.DepartureDate)


For I = 0 To DaysBetween

DoCmd.RunSQL "Insert Into [Suffolk Visitor Parking Log] " & _
"Values('" & Me.VisitorID & "', '" & Me.DateRequested + I & "');"



Next I

End Sub

Will it work if I have no primary key? How would I go about making the
primary key VisitorID and DateRequested? Thanx for your help. Fred

You cannot insert multiple rows to a table that have the same primary
key. The primary key must be unique. You could make the key
VisitorId, DateRequested. Having it as VisitorId will only work for
the first record that you insert, then when you try to insert the
second record, you will have a duplicate primary key and the insert
will fail.

Hope that helps!

It is not putting the multiple entries in the table and the primary key
is now VisitorId. It is still putting the one entry in so if I query on
a date in the in between the dates they need the parking for it will
not show up. Thanx Fred
Jeff L wrote:
Ok, you say it's not working. What's not working about it? Are you
getting error messages? Can you tell me what the Primary Key for your
table, [Suffolk Visitor Parking Log], is?


Fred wrote:
It is still not working and I have already made that suggestion to the
owner of the database. Here is what I have. Thanx for all your help

Private Sub DaysBetween_Click()


Dim DaysBetween As Integer, I As Integer


DaysBetween = DateDiff("d", Me.DateRequested, Me.DepartureDate)


For I = 0 To DaysBetween

DoCmd.RunSQL "Insert Into [Suffolk Visitor Parking Log] " & _
"Values('" & Me.Lastname & "', '" & Me.DateRequested + I & "');"



Next I

End Sub


Jeff L wrote:
What you have:
DoCmd.RunSQL "Insert Suffolk Visitor Parking Log" & _
"Values(""& Me.Lastname & "", '" & Me.DateRequested + I & "');"

What it should be:
DoCmd.RunSQL "Insert Into [Suffolk Visitor Parking Log] " & _
"Values('"& Me.Lastname & "', '" & Me.DateRequested + I & "');"

Just curious, but how are you going to distinguish between people with
the same last name? You should use a unique identifier, like
VisitorID, to distinguish who your visitors are.




Fred wrote:
Okay I still am fighting this so here is the code I am using

Private Function DaysBetween()


Dim DaysBetween As Integer, I As Integer


DaysBetween = DateDiff("d", Me.DateRequested, Me.DepartureDate)


For I = 0 To DaysBetween

DoCmd.RunSQL "Insert Suffolk Visitor Parking Log" & _
"Values(""& Me.Lastname & "", '" & Me.DateRequested + I & "');"

Next I


End Function
Again thanx for all the help
Jeff L wrote:
When you use a string value like Last Name, you need to put single
quotes around it. So it's Values('" & Me.LastName & "',"

Values([SingleQuote][DoubleQuote] & Me.YourFieldName &
[DoubleQuote][SingleQuote],[DoubleQuote]

When using numeric values, the single quotes are not needed.

If you are still having trouble, maybe you could post your code and let
me look at it.


Fred wrote:
I put the code in the Submit command button but the sql script is
giving me a compile end of statement error. I have a LastName instead
of a visitor id. It is highlighting the first Me. I really appreciate
the help Fred
Jeff L wrote:
Probably the best place would be to put it in the On Click event of a
Command Button. Use the button to make your parking reservation.

 

[Jeff L]

Your VisitorID is a number and thus your don't want the single quotes
around it when you are inserting it. It should be
DoCmd.RunSQL "Insert Into [Suffolk Visitor Parking Log] " & _
"Values(" & Me.VisitorID & ", '" & Me.DateRequested + I & "');"

If you are getting any error messages, it would be helpful if you could
post it with your response.

I have figured out the multilpe keys but still no luck I am posting the
script again in case I have messed it up. Thanx Fred

Private Sub DaysBetween_Click()


Dim DaysBetween As Integer, I As Integer


DaysBetween = DateDiff("d", Me.DateRequested, Me.DepartureDate)


For I = 0 To DaysBetween

DoCmd.RunSQL "Insert Into [Suffolk Visitor Parking Log] " & _
"Values('" & Me.VisitorID & "', '" & Me.DateRequested + I & "');"



Next I

End Sub

Will it work if I have no primary key? How would I go about making the
primary key VisitorID and DateRequested? Thanx for your help. Fred

You cannot insert multiple rows to a table that have the same primary
key. The primary key must be unique. You could make the key
VisitorId, DateRequested. Having it as VisitorId will only work for
the first record that you insert, then when you try to insert the
second record, you will have a duplicate primary key and the insert
will fail.

Hope that helps!


Fred wrote:
It is not putting the multiple entries in the table and the primary key
is now VisitorId. It is still putting the one entry in so if I query on
a date in the in between the dates they need the parking for it will
not show up. Thanx Fred
Jeff L wrote:
Ok, you say it's not working. What's not working about it? Are you
getting error messages? Can you tell me what the Primary Key for your
table, [Suffolk Visitor Parking Log], is?


Fred wrote:
It is still not working and I have already made that suggestion to the
owner of the database. Here is what I have. Thanx for all your help

Private Sub DaysBetween_Click()


Dim DaysBetween As Integer, I As Integer


DaysBetween = DateDiff("d", Me.DateRequested, Me.DepartureDate)


For I = 0 To DaysBetween

DoCmd.RunSQL "Insert Into [Suffolk Visitor Parking Log] " & _
"Values('" & Me.Lastname & "', '" & Me.DateRequested + I & "');"



Next I

End Sub


Jeff L wrote:
What you have:
DoCmd.RunSQL "Insert Suffolk Visitor Parking Log" & _
"Values(""& Me.Lastname & "", '" & Me.DateRequested + I & "');"

What it should be:
DoCmd.RunSQL "Insert Into [Suffolk Visitor Parking Log] " & _
"Values('"& Me.Lastname & "', '" & Me.DateRequested + I & "');"

Just curious, but how are you going to distinguish between people with
the same last name? You should use a unique identifier, like
VisitorID, to distinguish who your visitors are.




Fred wrote:
Okay I still am fighting this so here is the code I am using

Private Function DaysBetween()


Dim DaysBetween As Integer, I As Integer


DaysBetween = DateDiff("d", Me.DateRequested, Me.DepartureDate)


For I = 0 To DaysBetween

DoCmd.RunSQL "Insert Suffolk Visitor Parking Log" & _
"Values(""& Me.Lastname & "", '" & Me.DateRequested + I & "');"

Next I


End Function
Again thanx for all the help
Jeff L wrote:
When you use a string value like Last Name, you need to put single
quotes around it. So it's Values('" & Me.LastName & "',"

Values([SingleQuote][DoubleQuote] & Me.YourFieldName &
[DoubleQuote][SingleQuote],[DoubleQuote]

When using numeric values, the single quotes are not needed.

If you are still having trouble, maybe you could post your code and let
me look at it.


Fred wrote:
I put the code in the Submit command button but the sql script is
giving me a compile end of statement error. I have a LastName instead
of a visitor id. It is highlighting the first Me. I really appreciate
the help Fred
Jeff L wrote:
Probably the best place would be to put it in the On Click event of a
Command Button. Use the button to make your parking reservation.

 

[Fred]

I am getting no error codes it is just only putting a single record
into the database. Could we still have a problem with the primary key
because the DateReequested is the same also? Do I need a primary key?

Your VisitorID is a number and thus your don't want the single quotes
around it when you are inserting it. It should be
DoCmd.RunSQL "Insert Into [Suffolk Visitor Parking Log] " & _
"Values(" & Me.VisitorID & ", '" & Me.DateRequested + I & "');"

If you are getting any error messages, it would be helpful if you could
post it with your response.

I have figured out the multilpe keys but still no luck I am posting the
script again in case I have messed it up. Thanx Fred

Private Sub DaysBetween_Click()


Dim DaysBetween As Integer, I As Integer


DaysBetween = DateDiff("d", Me.DateRequested, Me.DepartureDate)


For I = 0 To DaysBetween

DoCmd.RunSQL "Insert Into [Suffolk Visitor Parking Log] " & _
"Values('" & Me.VisitorID & "', '" & Me.DateRequested + I & "');"



Next I

End Sub

Will it work if I have no primary key? How would I go about making the
primary key VisitorID and DateRequested? Thanx for your help. Fred
Jeff L wrote:
You cannot insert multiple rows to a table that have the same primary
key. The primary key must be unique. You could make the key
VisitorId, DateRequested. Having it as VisitorId will only work for
the first record that you insert, then when you try to insert the
second record, you will have a duplicate primary key and the insert
will fail.

Hope that helps!


Fred wrote:
It is not putting the multiple entries in the table and the primary key
is now VisitorId. It is still putting the one entry in so if I query on
a date in the in between the dates they need the parking for it will
not show up. Thanx Fred
Jeff L wrote:
Ok, you say it's not working. What's not working about it? Are you
getting error messages? Can you tell me what the Primary Key for your
table, [Suffolk Visitor Parking Log], is?


Fred wrote:
It is still not working and I have already made that suggestion to the
owner of the database. Here is what I have. Thanx for all your help

Private Sub DaysBetween_Click()


Dim DaysBetween As Integer, I As Integer


DaysBetween = DateDiff("d", Me.DateRequested, Me.DepartureDate)


For I = 0 To DaysBetween

DoCmd.RunSQL "Insert Into [Suffolk Visitor Parking Log] " & _
"Values('" & Me.Lastname & "', '" & Me.DateRequested + I & "');"



Next I

End Sub


Jeff L wrote:
What you have:
DoCmd.RunSQL "Insert Suffolk Visitor Parking Log" & _
"Values(""& Me.Lastname & "", '" & Me.DateRequested + I & "');"

What it should be:
DoCmd.RunSQL "Insert Into [Suffolk Visitor Parking Log] " & _
"Values('"& Me.Lastname & "', '" & Me.DateRequested + I & "');"

Just curious, but how are you going to distinguish between people with
the same last name? You should use a unique identifier, like
VisitorID, to distinguish who your visitors are.




Fred wrote:
Okay I still am fighting this so here is the code I am using

Private Function DaysBetween()


Dim DaysBetween As Integer, I As Integer


DaysBetween = DateDiff("d", Me.DateRequested, Me.DepartureDate)


For I = 0 To DaysBetween

DoCmd.RunSQL "Insert Suffolk Visitor Parking Log" & _
"Values(""& Me.Lastname & "", '" & Me.DateRequested + I & "');"

Next I


End Function
Again thanx for all the help
Jeff L wrote:
When you use a string value like Last Name, you need to put single
quotes around it. So it's Values('" & Me.LastName & "',"

Values([SingleQuote][DoubleQuote] & Me.YourFieldName &
[DoubleQuote][SingleQuote],[DoubleQuote]

When using numeric values, the single quotes are not needed.

If you are still having trouble, maybe you could post your code and let
me look at it.


Fred wrote:
I put the code in the Submit command button but the sql script is
giving me a compile end of statement error. I have a LastName instead
of a visitor id. It is highlighting the first Me. I really appreciate
the help Fred
Jeff L wrote:
Probably the best place would be to put it in the On Click event of a
Command Button. Use the button to make your parking reservation.

 

[Jeff L]

OK, I tweaked it a bit. Try this:

Dim DaysBetween As Integer, I As Integer, HoldDate As Date

DaysBetween = DateDiff("d", Me.StartDate, Me.EndDate)

For I = 0 To DaysBetween

HoldDate = Me.StartDate + I

DoCmd.RunSQL "Insert into Table3(VisitorID, DateRequested) " & _
"Values(" & Me.ID & ", '" & HoldDate & "');"
Next I

You will need to delete whatever data you have in your table.
Leave the Primary key the way it is.

I am getting no error codes it is just only putting a single record
into the database. Could we still have a problem with the primary key
because the DateReequested is the same also? Do I need a primary key?

Your VisitorID is a number and thus your don't want the single quotes
around it when you are inserting it. It should be
DoCmd.RunSQL "Insert Into [Suffolk Visitor Parking Log] " & _
"Values(" & Me.VisitorID & ", '" & Me.DateRequested + I & "');"

If you are getting any error messages, it would be helpful if you could
post it with your response.

I have figured out the multilpe keys but still no luck I am posting the
script again in case I have messed it up. Thanx Fred

Private Sub DaysBetween_Click()


Dim DaysBetween As Integer, I As Integer


DaysBetween = DateDiff("d", Me.DateRequested, Me.DepartureDate)


For I = 0 To DaysBetween

DoCmd.RunSQL "Insert Into [Suffolk Visitor Parking Log] " & _
"Values('" & Me.VisitorID & "', '" & Me.DateRequested + I & "');"



Next I

End Sub


Fred wrote:
Will it work if I have no primary key? How would I go about making the
primary key VisitorID and DateRequested? Thanx for your help. Fred
Jeff L wrote:
You cannot insert multiple rows to a table that have the same primary
key. The primary key must be unique. You could make the key
VisitorId, DateRequested. Having it as VisitorId will only work for
the first record that you insert, then when you try to insert the
second record, you will have a duplicate primary key and the insert
will fail.

Hope that helps!


Fred wrote:
It is not putting the multiple entries in the table and the primary key
is now VisitorId. It is still putting the one entry in so if I query on
a date in the in between the dates they need the parking for it will
not show up. Thanx Fred
Jeff L wrote:
Ok, you say it's not working. What's not working about it? Are you
getting error messages? Can you tell me what the Primary Key for your
table, [Suffolk Visitor Parking Log], is?


Fred wrote:
It is still not working and I have already made that suggestion to the
owner of the database. Here is what I have. Thanx for all your help

Private Sub DaysBetween_Click()


Dim DaysBetween As Integer, I As Integer


DaysBetween = DateDiff("d", Me.DateRequested, Me.DepartureDate)


For I = 0 To DaysBetween

DoCmd.RunSQL "Insert Into [Suffolk Visitor Parking Log] " & _
"Values('" & Me.Lastname & "', '" & Me.DateRequested + I & "');"



Next I

End Sub


Jeff L wrote:
What you have:
DoCmd.RunSQL "Insert Suffolk Visitor Parking Log" & _
"Values(""& Me.Lastname & "", '" & Me.DateRequested + I & "');"

What it should be:
DoCmd.RunSQL "Insert Into [Suffolk Visitor Parking Log] " & _
"Values('"& Me.Lastname & "', '" & Me.DateRequested + I & "');"

Just curious, but how are you going to distinguish between people with
the same last name? You should use a unique identifier, like
VisitorID, to distinguish who your visitors are.




Fred wrote:
Okay I still am fighting this so here is the code I am using

Private Function DaysBetween()


Dim DaysBetween As Integer, I As Integer


DaysBetween = DateDiff("d", Me.DateRequested, Me.DepartureDate)


For I = 0 To DaysBetween

DoCmd.RunSQL "Insert Suffolk Visitor Parking Log" & _
"Values(""& Me.Lastname & "", '" & Me.DateRequested + I & "');"

Next I


End Function
Again thanx for all the help
Jeff L wrote:
When you use a string value like Last Name, you need to put single
quotes around it. So it's Values('" & Me.LastName & "',"

Values([SingleQuote][DoubleQuote] & Me.YourFieldName &
[DoubleQuote][SingleQuote],[DoubleQuote]

When using numeric values, the single quotes are not needed.

If you are still having trouble, maybe you could post your code and let
me look at it.


Fred wrote:
I put the code in the Submit command button but the sql script is
giving me a compile end of statement error. I have a LastName instead
of a visitor id. It is highlighting the first Me. I really appreciate
the help Fred
Jeff L wrote:
Probably the best place would be to put it in the On Click event of a
Command Button. Use the button to make your parking reservation.

 

[Fred]

Hey Jeff
Can I just send you the database it is only 4 megs so that you can find
out what I am doing wrong. I was brought in at the end of this project.

OK, I tweaked it a bit. Try this:

Dim DaysBetween As Integer, I As Integer, HoldDate As Date

DaysBetween = DateDiff("d", Me.StartDate, Me.EndDate)

For I = 0 To DaysBetween

HoldDate = Me.StartDate + I

DoCmd.RunSQL "Insert into Table3(VisitorID, DateRequested) " & _
"Values(" & Me.ID & ", '" & HoldDate & "');"
Next I

You will need to delete whatever data you have in your table.
Leave the Primary key the way it is.

I am getting no error codes it is just only putting a single record
into the database. Could we still have a problem with the primary key
because the DateReequested is the same also? Do I need a primary key?

Your VisitorID is a number and thus your don't want the single quotes
around it when you are inserting it. It should be
DoCmd.RunSQL "Insert Into [Suffolk Visitor Parking Log] " & _
"Values(" & Me.VisitorID & ", '" & Me.DateRequested + I & "');"

If you are getting any error messages, it would be helpful if you could
post it with your response.



Fred wrote:
I have figured out the multilpe keys but still no luck I am posting the
script again in case I have messed it up. Thanx Fred

Private Sub DaysBetween_Click()


Dim DaysBetween As Integer, I As Integer


DaysBetween = DateDiff("d", Me.DateRequested, Me.DepartureDate)


For I = 0 To DaysBetween

DoCmd.RunSQL "Insert Into [Suffolk Visitor Parking Log] " & _
"Values('" & Me.VisitorID & "', '" & Me.DateRequested + I & "');"



Next I

End Sub


Fred wrote:
Will it work if I have no primary key? How would I go about making the
primary key VisitorID and DateRequested? Thanx for your help. Fred
Jeff L wrote:
You cannot insert multiple rows to a table that have the same primary
key. The primary key must be unique. You could make the key
VisitorId, DateRequested. Having it as VisitorId will only work for
the first record that you insert, then when you try to insert the
second record, you will have a duplicate primary key and the insert
will fail.

Hope that helps!


Fred wrote:
It is not putting the multiple entries in the table and the primary key
is now VisitorId. It is still putting the one entry in so if I query on
a date in the in between the dates they need the parking for it will
not show up. Thanx Fred
Jeff L wrote:
Ok, you say it's not working. What's not working about it? Are you
getting error messages? Can you tell me what the Primary Key for your
table, [Suffolk Visitor Parking Log], is?


Fred wrote:
It is still not working and I have already made that suggestion to the
owner of the database. Here is what I have. Thanx for all your help

Private Sub DaysBetween_Click()


Dim DaysBetween As Integer, I As Integer


DaysBetween = DateDiff("d", Me.DateRequested, Me.DepartureDate)


For I = 0 To DaysBetween

DoCmd.RunSQL "Insert Into [Suffolk Visitor Parking Log] " & _
"Values('" & Me.Lastname & "', '" & Me.DateRequested + I & "');"



Next I

End Sub


Jeff L wrote:
What you have:
DoCmd.RunSQL "Insert Suffolk Visitor Parking Log" & _
"Values(""& Me.Lastname & "", '" & Me.DateRequested + I & "');"

What it should be:
DoCmd.RunSQL "Insert Into [Suffolk Visitor Parking Log] " & _
"Values('"& Me.Lastname & "', '" & Me.DateRequested + I & "');"

Just curious, but how are you going to distinguish between people with
the same last name? You should use a unique identifier, like
VisitorID, to distinguish who your visitors are.




Fred wrote:
Okay I still am fighting this so here is the code I am using

Private Function DaysBetween()


Dim DaysBetween As Integer, I As Integer


DaysBetween = DateDiff("d", Me.DateRequested, Me.DepartureDate)


For I = 0 To DaysBetween

DoCmd.RunSQL "Insert Suffolk Visitor Parking Log" & _
"Values(""& Me.Lastname & "", '" & Me.DateRequested + I & "');"

Next I


End Function
Again thanx for all the help
Jeff L wrote:
When you use a string value like Last Name, you need to put single
quotes around it. So it's Values('" & Me.LastName & "',"

Values([SingleQuote][DoubleQuote] & Me.YourFieldName &
[DoubleQuote][SingleQuote],[DoubleQuote]

When using numeric values, the single quotes are not needed.

If you are still having trouble, maybe you could post your code and let
me look at it.


Fred wrote:
I put the code in the Submit command button but the sql script is
giving me a compile end of statement error. I have a LastName instead
of a visitor id. It is highlighting the first Me. I really appreciate
the help Fred
Jeff L wrote:
Probably the best place would be to put it in the On Click event of a
Command Button. Use the button to make your parking reservation.

 

[Duane Hookom]

Did you ever take a look at the "recurring" sample at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane?
If so, did it kinda meet your needs or what issues did you have with it?
--
Duane Hookom
MS Access MVP

Hey Jeff
Can I just send you the database it is only 4 megs so that you can find
out what I am doing wrong. I was brought in at the end of this project.

OK, I tweaked it a bit. Try this:

Dim DaysBetween As Integer, I As Integer, HoldDate As Date

DaysBetween = DateDiff("d", Me.StartDate, Me.EndDate)

For I = 0 To DaysBetween

HoldDate = Me.StartDate + I

DoCmd.RunSQL "Insert into Table3(VisitorID, DateRequested) " & _
"Values(" & Me.ID & ", '" & HoldDate & "');"
Next I

You will need to delete whatever data you have in your table.
Leave the Primary key the way it is.

I am getting no error codes it is just only putting a single record
into the database. Could we still have a problem with the primary key
because the DateReequested is the same also? Do I need a primary key?
Jeff L wrote:
Your VisitorID is a number and thus your don't want the single quotes
around it when you are inserting it. It should be
DoCmd.RunSQL "Insert Into [Suffolk Visitor Parking Log] " & _
"Values(" & Me.VisitorID & ", '" & Me.DateRequested + I & "');"

If you are getting any error messages, it would be helpful if you
could
post it with your response.



Fred wrote:
I have figured out the multilpe keys but still no luck I am posting
the
script again in case I have messed it up. Thanx Fred

Private Sub DaysBetween_Click()


Dim DaysBetween As Integer, I As Integer


DaysBetween = DateDiff("d", Me.DateRequested, Me.DepartureDate)


For I = 0 To DaysBetween

DoCmd.RunSQL "Insert Into [Suffolk Visitor Parking Log] " & _
"Values('" & Me.VisitorID & "', '" & Me.DateRequested + I & "');"



Next I

End Sub


Fred wrote:
Will it work if I have no primary key? How would I go about
making the
primary key VisitorID and DateRequested? Thanx for your help.
Fred
Jeff L wrote:
You cannot insert multiple rows to a table that have the same
primary
key. The primary key must be unique. You could make the key
VisitorId, DateRequested. Having it as VisitorId will only
work for
the first record that you insert, then when you try to insert
the
second record, you will have a duplicate primary key and the
insert
will fail.

Hope that helps!


Fred wrote:
It is not putting the multiple entries in the table and the
primary key
is now VisitorId. It is still putting the one entry in so if
I query on
a date in the in between the dates they need the parking for
it will
not show up. Thanx Fred
Jeff L wrote:
Ok, you say it's not working. What's not working about it?
Are you
getting error messages? Can you tell me what the Primary
Key for your
table, [Suffolk Visitor Parking Log], is?


Fred wrote:
It is still not working and I have already made that
suggestion to the
owner of the database. Here is what I have. Thanx for all
your help

Private Sub DaysBetween_Click()


Dim DaysBetween As Integer, I As Integer


DaysBetween = DateDiff("d", Me.DateRequested,
Me.DepartureDate)


For I = 0 To DaysBetween

DoCmd.RunSQL "Insert Into [Suffolk Visitor Parking Log] "
& _
"Values('" & Me.Lastname & "', '" & Me.DateRequested + I
& "');"



Next I

End Sub


Jeff L wrote:
What you have:
DoCmd.RunSQL "Insert Suffolk Visitor Parking Log" & _
"Values(""& Me.Lastname & "", '" & Me.DateRequested + I
& "');"

What it should be:
DoCmd.RunSQL "Insert Into [Suffolk Visitor Parking Log]
" & _
"Values('"& Me.Lastname & "', '" & Me.DateRequested + I
& "');"

Just curious, but how are you going to distinguish
between people with
the same last name? You should use a unique
identifier, like
VisitorID, to distinguish who your visitors are.




Fred wrote:
Okay I still am fighting this so here is the code I
am using

Private Function DaysBetween()


Dim DaysBetween As Integer, I As Integer


DaysBetween = DateDiff("d", Me.DateRequested,
Me.DepartureDate)


For I = 0 To DaysBetween

DoCmd.RunSQL "Insert Suffolk Visitor Parking Log" & _
"Values(""& Me.Lastname & "", '" & Me.DateRequested +
I & "');"

Next I


End Function
Again thanx for all the help
Jeff L wrote:
When you use a string value like Last Name, you
need to put single
quotes around it. So it's Values('" & Me.LastName
& "',"

Values([SingleQuote][DoubleQuote] &
Me.YourFieldName &
[DoubleQuote][SingleQuote],[DoubleQuote]

When using numeric values, the single quotes are
not needed.

If you are still having trouble, maybe you could
post your code and let
me look at it.


Fred wrote:
I put the code in the Submit command button but
the sql script is
giving me a compile end of statement error. I
have a LastName instead
of a visitor id. It is highlighting the first Me.
I really appreciate
the help Fred
Jeff L wrote:
Probably the best place would be to put it in
the On Click event of a
Command Button. Use the button to make your
parking reservation.