You are here:

Using MS Access/Re: For ... Next - Result!

Advertisement


Question
QUESTION: Hi Scott,

I tried running your code, after adapting it to your recommendations, and it returned error 3061 - Too few parameters - Expected 1

Here's the code as it stands now...

Dim strSQL As String
Dim i As Integer

For i = 1 To DateDiff("d", Me.StayDate, Me.DepartDate) - 1
   strSQL = "INSERT INTO TblBookings (GuestID, StayDate,DepartDate, RmNumber) " & _
         "VALUES(" & Me.GuestName & ", #" & DateAdd("d", i, Me.StayDate) & "#, #" & _
         Me.DepartDate & "#, " & Me.RmNumber & ");"
   CurrentDb.Execute strSQL

Next i

Any suggestions?

TIA

Pete

ANSWER: Did you make the change in the Guestname Control so it returns a numeric value for GuestID? Also, is RmNumber a numeric value?

If either of those two are text values, you need to concatenate in single quotes around the value.

If that isn't the problem, then place Debug.Print strSQL before the CurrentDB.Execute line. This will print the rsulting SQL statement to the Immediate Window. You can then view to see if anything looks wrong and/or copy and paste it into SQL view in Query Design. Then see if it gives an error message when you switch to Design mode.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Hi Scott,

Still returns 'expected 1'.   By the way, RmNumber is numeric, GuestID has to stay alpha numeric - by request.

Room numbers follow simple 101, 102, etc pattern;
GuestID's can be GN0011, GN0012, BN0045, etc

I think I put the single quotes around the correct field in the code, not sure where to go from here...

Here's the code again, asI have it behind the command button:

For i = 1 To DateDiff("d", Me.StayDate, Me.DepartDate) - 1
   strSQL = "INSERT INTO TblBookings ('GuestID', StayDate,DepartDate, RmNumber) " & _
         "VALUES(" & Me.GuestID & ", #" & DateAdd("d", i, Me.StayDate) & "#, #" & _
         Me.DepartDate & "#, " & Me.RmNumber & ");"
   Debug.Print strSQL
   CurrentDb.Execute strSQL

Next i


TIA

Pete

ANSWER: By whose request does GuestID have to be alpha numeric? What does the the 2 letter prefix stand for?

However, if guestID is alpha/numeric then the code should be:

For i = 1 To DateDiff("d", Me.StayDate, Me.DepartDate) - 1
   strSQL = "INSERT INTO TblBookings (GuestID, StayDate,DepartDate, RmNumber) " & _
         "VALUES('" & Me.GuestID & "', #" & DateAdd("d", i, Me.StayDate) & "#, #" & _
         Me.DepartDate & "#, " & Me.RmNumber & ");"
   Debug.Print strSQL
   CurrentDb.Execute strSQL

Next i


If that still doesn't work, then what does the Debug.Print show?

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Hi Scott,

that worked perefectly - thank you!

The request for the alphanumeric GuestID was from the hotel owner who wants to differentiate between his 'cattle class' clients and his VIP clients - of which he has many!

Now, that gave me the thought that I could also use a simplified version of the For ... Next to add a new client to [TblGuests], where I want to insert the name, address, country, nationality and passport VISA number from the same form used for assigning the room.

Fields (ALL TEXT FIELDS) to be inserted into are

[Salutation]
[GivenName]
[MiddleName]
[Surname]
[Addr1]
[Addr2]
[suburb]
[State]
[zipcode]
[Country]
[Nationality]
[PassportVisaNumber]
[PVNExpiryDate]

Care to help me out with the code?  I'd attempt it myself, but know I will get something wrong :)

Thanks in anticipation

Pete

Answer
A For Next doesn't make sense here since much of the info wouldn't be repeated. If it was repeated, then you should have several tables. You should have an address table and a guest table. So if you have multiple guests you can have one address record.

I would have a Continuous form subform on the booking form to enter (or select) the guest info.

As for the GuestID, I'm assuming there is a field in the table that indicates whether a guest is VIP or not (This should be in the Guests table). In that storing this Guest code is redundant. Instead, I would use an Autonumber as your primary key and then just concatenate the prefix unto that for display purposes.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.