Solved strSql - Object Required (1 Viewer)

[kevnaff]

Hi All.

I am creating a new equipment library database. The user will log when they are taking a piece of equipment from the library using a booking out form called 'PoolBookings'. This saves a record of the equipment's asset number, their name and department, and the current date and time. It does so in a table called 'PoolBookings'.

When the user returns the equipment, they then open up the returns form called 'PoolReturns'. On this form they enter the same information as the booking out form. The only difference being, that the equipment asset number text box for them to type in to is now unbound and the textbox is called 'CodeNoReturning'. Once they hit the complete button on the form, I want to look through all records in the 'PoolBookings' table and find the last record with the same asset number that is in the 'CodeNoReturning' box, that also has a null value in the DateIn field.

Below is an example of the fields in the table and also a view of the Returns form.

The code currently behind the Complete Returning button is...

Private Sub CommandCompleteReturning_Click()

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

strSql = "SELECT * FROM PoolBookings WHERE [CodeNo] = " & "'" & Me.CodeNoReturning & "'"
Set db = CurrentDb()
Set REC = db.OpenRecordset(strSql, dbOpenDynaset)
REC.MoveLast
TotalRecords = REC.RecordCount
REC.Close

Message = "MediPool has found " & TotalRecords & _
            " records in the Pool Bookings table" _
            & Chr(10) & "Do you wish to continue?"
Title = "MediPool Code Finder"
Response = MsgBox(Message, vbOKCancel, Title)

End Sub

This works OK at the moment, however I want to add an AND to the strSql to help find records with the same CodeNo, but also have not already been returned, so the DateIn value should be null. When I add AND DateIn Is Null to the end of the StrSql I get a message saying ' Run-Time Error 424 'Object Required' '.

I can't figure out how to get around this.

Would anybody know why this is happening?

I think once I get around this I will be OK with the rest of the process of updating the records data.

Thanks in advance

 

[Minty]

Your error is probably caused by the way you are concatenating the strSQL, but you haven't showed us the non-working example you tried.
That's also quite a long winded way of doing what could also be achieved with a simple DCount()

TotalRecords = DCount("*","PoolBookings","[CodeNo] = '" & Me.CodeNoReturning & "' AND [DateIn] is Null")

 

[kevnaff]

Your error is probably caused by the way you are concatenating the strSQL, but you haven't showed us the non-working example you tried.
That's also quite a long winded way of doing what could also be achieved with a simple DCount()

TotalRecords = DCount("*","PoolBookings","[CodeNo] = '" & Me.CodeNoReturning & "' AND [DateIn] is Null")

Thanks Minty that has certainly worked a lot easier.

Is there a way to select the most recent BookingID from the records the DCount finds?

I was adding the AND [DateIn] is Null to the end of the strSql like so....

strSql = "SELECT * FROM PoolBookings WHERE [CodeNo] = " & "'" & Me.CodeNoReturning & "'" And [DateIn] Is Null

 

[Gasman]

Debug.Print strSQL and see what you get?

 

[Minty]

Please add Option Explicit to the top of all your code modules.
Then compile your code and it should be obvious that your string isn't correct.
As Gasman stated always use Debug.Print strSql and it will be clear as day where your error lies.

To answer your second query I would probably do something like

TotalRecords = DCount("*","PoolBookings","[CodeNo] = '" & Me.CodeNoReturning & "' AND [DateIn] is Null")
If TotalRecords > 0 then
    LastID = DMax("BookingID ","PoolBookings","[CodeNo] = '" & Me.CodeNoReturning & "' AND [DateIn] is Null")
End If

This will only work if your BookingID is a sequential auto number.

You could possibly reduce that to a single DMax with some alternative logic, but baby steps.

 

[kevnaff]

Please add Option Explicit to the top of all your code modules.
Then compile your code and it should be obvious that your string isn't correct.
As Gasman stated always use Debug.Print strSql and it will be clear as day where your error lies.

To answer your second query I would probably do something like

TotalRecords = DCount("*","PoolBookings","[CodeNo] = '" & Me.CodeNoReturning & "' AND [DateIn] is Null")
If TotalRecords > 0 then
    LastID = DMax("BookingID ","PoolBookings","[CodeNo] = '" & Me.CodeNoReturning & "' AND [DateIn] is Null")
End If

This will only work if your BookingID is a sequential auto number.

You could possibly reduce that to a single DMax with some alternative logic, but baby steps.

Yes my BookingID is a sequential auto number.

What I want to do now is...

If TotalRecords > 0, I want to locate the record with the BookingID which = LastID, and then update the DateIn, TimeIn etc.

I have tried to do this with the following code but I am getting a Run-time error 3061, Too few parameters. Expected 1. It highlights 'Set REC = db.OpenRecordset(strSql, dbOpenDynaset)'

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

TotalRecords = DCount("*", "PoolBookings", "[CodeNo] = '" & Me.CodeNoReturning & "' AND [DateIn] is Null")
LastID = DMax("BookingID ", "PoolBookings", "[CodeNo] = '" & Me.CodeNoReturning & "' AND [DateIn] is Null")


'Update PoolBookings with Returns Information

strSql = "SELECT * FROM [PoolBookings] WHERE [BookingID] = LastID"
Set db = CurrentDb()
Set REC = db.OpenRecordset(strSql, dbOpenDynaset)
Set REC2 = db.OpenRecordset("ImportEquipment", dbOpenDynaset)
REC.Edit
REC("DateIn") = Date
REC("TimeIn") = Time()
REC("BookedInBy") = Me.BookedInBy
REC("DepartmentBookingIn") = Me.DepartmentBookingIn
REC.Update
REC.Close

I can't figure out why this may be.

 

[kevnaff]

Debug.Print strSQL and see what you get?

Sorry Gasman, I'm not sure how to do this.

I've tried looking at a few things online but can't figure out how to go about this.

 

[adhoustonj]

strSql = "SELECT * FROM [PoolBookings] WHERE [BookingID] = LastID"

It should be this since you need to join the variable into the SQL statement.

strSql = "SELECT * FROM [PoolBookings] WHERE [BookingID] =  " & LastID & ""

 

[Gasman]

Sorry Gasman, I'm not sure how to do this.

I've tried looking at a few things online but can't figure out how to go about this.

You are trying to create code with one hand tied behind your back and wearing an eyepatch.

In your code once you construct the strSQL you then
Debug.Print strSQL'This goes on the next line.

This will output to the immediate window, which you can access with Ctrl + G
In the immediate window you can test commands and also display other variable/control values.

Google setting breakpoints and walking your code line by line with F8.
See the Debug toolbar button in the IDE window.

 

[kevnaff]

It should be this since you need to join the variable into the SQL statement.

strSql = "SELECT * FROM [PoolBookings] WHERE [BookingID] =  " & LastID & ""

Perfect, works a treat.

 

[kevnaff]

You are trying to create code with one hand tied behind your back and wearing an eyepatch.

In your code once you construct the strSQL you then
Debug.Print strSQL'This goes on the next line.

This will output to the immediate window, which you can access with Ctrl + G
In the immediate window you can test commands and also display other variable/control values.

Google setting breakpoints and walking your code line by line with F8.
See the Debug toolbar button in the IDE window.

Thanks for this, I'll start doing this.

 

[Minty]

Thanks for this, I'll start doing this.

I still do this when I'm developing and I've been writing SQL expressions for longer than I care to remember.
It's very easy to get the concatenation wrong especially when they get complicated with strings and dates involved.