strSql errors

[kevnaff]

Hi all,

I have a command button that I want to look at the table 'ImportEquipment' and find out whether [CodeNo] from the table 'PoolBookings' exists in both.

If this CodeNo exists, then I want to change the [Location] field in the 'ImportEquipment' table to Me.DepartmentBookingOut

At the moment it is highlights my strsql as the error and I am getting a 'Too few parameters. Expected 1.'

The [Code No] field that exists in 'ImportEquipment', has a space in the Code No as this was created many years ago by somebody else and I've not yet got round to changing this and all of the code relating to this. I understand that if a field has a space, then it must have brackets around it, so maybe I've done this wrong.

There must be something wrong with the strSql = "SELECT * FROM [ImportEquipment] WHERE ([Code No]) = " & Me.CodeNo & ""

But I can't figure out what this is.

The full code is below.

Dim db As DAO.Database
Dim REC As Recordset
Dim strSql As String
Dim n As Integer
Dim TotalRecords As Integer

TotalRecords = DCount("CodeNo", "ImportEquipment", "([Code No]) = '" & Me.CodeNo & "'")

If TotalRecords > 0 Then

'Update PoolBookings with Returns Information
strSql = "SELECT * FROM [ImportEquipment] WHERE ([Code No]) =  " & Me.CodeNo & ""
Set db = CurrentDb()
Set REC = db.OpenRecordset(strSql, dbOpenDynaset)

REC("Location") = Me.DepartmentBookingOut

Else

Message = "No Record Found"

End If

DoCmd.Close acForm, "PoolBookings"

Thanks all in advance

 

[LarryE]

strSql = "SELECT * FROM [ImportEquipment] WHERE ([Code No]) = " & Me.CodeNo & ""

You might need a semi-colon at the end of the string ";"

 

[theDBguy]

Debug.Print your sql statement and plug it into a query and see what error you get. You should get a parameter prompt.

 

[cheekybuddha]

From your initial DCount() it appears that CodeNo is a string datatype, yet you fail to delimit it in your SQL statement.

You don't actually seem to be updating table PoolBookings anywhere. (You update table ImportEquipment.Location)

 

[cheekybuddha]

You can probably just use a single query to do what you need:

Dim strSQL As String

strSQL = "UPDATE ImportEquipment e " & _
         "INNER JOIN PoolBookings p " & _
                 "ON e.[Code No] = p.[Code No] " & _
           "SET e.Location = '" & Me.DepartmentBookingOut & "' " & _
         "WHERE e.[Code No] = '" & Me.CodeNo & "';"
With CurrentDb
  .Execute strSQL, dbFailOnError
  If .RecordsAffected = 0 Then
    Message = "No Record Found"
  End If
End With

(NB Untested!)

 

[ebs17]

TotalRecords = DCount("CodeNo", "ImportEquipment", "([Code No]) = '" & Me.CodeNo & "'")
' ...
strSql = "SELECT * FROM [ImportEquipment] WHERE ([Code No]) = " & Me.CodeNo & ""

Please put the dice down. You must already know and decide what data type [Code No] has and how to use it.
What does CodeNo versus Code No mean?

 

[kevnaff]

You might need a semi-colon at the end of the string ";"

Unfortunately this didn't work. I added the ; like so

strSQL = "SELECT * FROM [ImportEquipment] WHERE ([Code No]) = " & Me.CodeNo & "";

and got a Syntax error.

 

[LarryE]

Put the semi-colon between the quote marks: ";"

 

[kevnaff]

TotalRecords = DCount("CodeNo", "ImportEquipment", "([Code No]) = '" & Me.CodeNo & "'")
' ...
strSql = "SELECT * FROM [ImportEquipment] WHERE ([Code No]) = " & Me.CodeNo & ""

Please put the dice down. You must already know and decide what data type [Code No] has and how to use it.
What does CodeNo versus Code No mean?

Hi Ebs.

I have PoolBookings table which includes the [CodeNo] field. This table is keeping a record of any time a device is booked in and out. And each device has a unique CodeNo.

All information for each device is contained in the ImportEquipment table where the [Code No] is the primary key.

The user fills in a form, with the record source 'PoolBookings', whenever they book out a device, and enter the [CodeNo] and also the [DepartmentBookingOut] to where they are taking it.

When they click a command button to complete the booking out process, I want to update the [Location] field in the ImportEquipment table, to match the [DepartmentBookingOut] from the 'PoolBookings' table.

I might be making this more difficult than it actually is.

 

[kevnaff]

Debug.Print your sql statement and plug it into a query and see what error you get. You should get a parameter prompt.

Sorry DBguy. I don't know how to do this.

I have removed everything and just have this code...

Dim sql As String

strSQL = "SELECT * FROM [ImportEquipment] WHERE ([Code No]) =  " & Me.CodeNo & ""
Debug.Print

I have opened the Immediate Window, but don't know how to get something to show up there.

I've never used this before but it's something I definitely need to learn how to.

Thanks

 

[ebs17]

what data type [Code No] has

 

[theDBguy]

Sorry DBguy. I don't know how to do this.

I have removed everything and just have this code...

Dim sql As String

strSQL = "SELECT * FROM [ImportEquipment] WHERE ([Code No]) =  " & Me.CodeNo & ""
Debug.Print

I have opened the Immediate Window, but don't know how to get something to show up there.

I've never used this before but it's something I definitely need to learn how to.

Thanks

Try using:

Debug.Print strSQL

 

[Pat Hartman]

@kevnaff #4 has the probable answer and #3 told you how to see the error.

 

[The_Doc_Man]

You have a strange construct that makes me suspicious. WHERE ([Code No]) =

What bothers me is that the parentheses around the brackets makes [Code No] an expression, not a field reference. It is still legal but I am not sure how that expression will be evaluated. Stated another way, this is syntactically legal but semantically questionable.

 

[Josef P.]

[OT]

You have a strange construct that makes me suspicious. WHERE ([Code No]) =

This is used by the Access query editor.

Example:

SELECT ...
FROM Table1
WHERE (((Table1.T)="A"));

 

[Gasman]

It does not use the [ and ] then?

 

[Josef P.]

SELECT Table1.id, Table1.[T x]
FROM Table1
WHERE (((Table1.[T x])="A"));

The query editor loves brackets.

 

[kevnaff]

You have a strange construct that makes me suspicious. WHERE ([Code No]) =

What bothers me is that the parentheses around the brackets makes [Code No] an expression, not a field reference. It is still legal but I am not sure how that expression will be evaluated. Stated another way, this is syntactically legal but semantically questionable.

The database was built by somebody else starting in 1998. I'm hoping to go through it one day and try to remove any instances of fields with spaces in them.

I read somewhere that [Code No] is treated differently than if it was just [CodeNo] and putting in the parentheses would help the issue.

Maybe this is only true in some instances.

 

[kevnaff]

From your initial DCount() it appears that CodeNo is a string datatype, yet you fail to delimit it in your SQL statement.

You don't actually seem to be updating table PoolBookings anywhere. (You update table ImportEquipment.Location)

Hi cheekybuddha

The command button is on the PoolBookings form. The PoolBookings table is updated as the user enters the information.

The user enters the following information in to the form [CodeNo], [DepartmentBookingOut], [BookedOutBy]

When they hit the Complete command button, I want it to check if the [CodeNo] that they have entered, exists in the ImportEquipment table. I was trying to do this with the DCount. The field that it would match in the ImportEquipment table is [Code No], as you can see it has a space, this is not an error.

If the [CodeNo] matches a [Code No], then I want to update this record's [Location] in the ImportEquipment table.

I feel like I may be over complicating this, as it doesn't seem too difficult on the surface.

Thanks for your help.

 

[cheekybuddha]

My previous suggestion (or a variation) ought to work if you use the correct field names for each table: CodeNo vs [Code No] (who named these inconsistently? What a nightmare!)

Storing the same data in different tables seems a bit of a design smell to me, but if there is good reason to store the Location in ImportEquipment rather than use a query on the two tables joined to find the Location, you can try something like:

Dim strSQL As String

strSQL = "UPDATE ImportEquipment e " & _
         "INNER JOIN PoolBookings p " & _
                 "ON e.[Code No] = p.CodeNo " & _
           "SET e.Location = p.Location " & _
         "WHERE p.CodeNo = '" & Me.CodeNo & "';"
With CurrentDb
  .Execute strSQL, dbFailOnError
  If .RecordsAffected = 0 Then
    MsgBox "No Record Found"
  End If
End With

This will update the Location field in ImportEquipment with the Location field value in PoolBookings if the the CodeNo in PoolBookings exists in the [Code No] field in ImportEquipment (it will be found via the INNER JOIN).

If the [Code No] is not present in ImportEquipment nothing will happen.

So, it tests whether any update occurred (via .RecordsAffected) - if so, continue; if not, notify that no match was found.

(Also assumes that CodeNo/[Code No] are string datatypes - I'm not sure you've clarified that above)