strSql errors

[kevnaff]

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)

You're right. I don't know why I am not just creating a query to join the two tables.

I have created a query called QueryPoolBookings with PoolBookings and ImportEquipment tables, and included all fields from PoolBookings and just the Location field from ImportEquipment. I have changed the form's record source to QueryPoolBookings.

In the DCount, I have used EquipID as

The complete button now has this behind it which works as it should...

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

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

If TotalRecords > 0 Then

'Update Location in query, which will reflect in Equipment Inventory
Me.Location = Me.DepartmentBookingOut

Else

Message = "This Asset# has not been found in the Equipment Inventory"
Title = "MediPool Asset# Check"
Response = MsgBox(Message, vbOKCancel, Title)

End If

DoCmd.Close acForm, "PoolBookings"

Thanks all for your help

 

[kevnaff]

Whilst my code is working OK.

It currently updates the [Location] based on the [DepartmentBookingOut] field.

In the PoolBookings form, the user selects the [DepartmentBookingOut] from a List Box. Almost all departments should be in the box, however in case they aren't, I added another field in the PoolBookings table called [DepartmentBookingOutNotInList].

The way the code is set up at the moment, will work perfectly as long as the user selects the correct [DepartmentBookingOut].

I wanted to use an IF statement within an IF statement. I don't know whether this is possible.

I want the IF statement to look at whether the [DeparmentBookingOut] is null, so I'm guessing it would look like this...

Dim db As DAO.Database

If [DepartmentBookingOut] Is Null Then

Me.Location = Me.DepartmentBookingOutNotInList

Else

Me.Location = Me.DepartmentBookingOut

End If

When I run this I get an Object Required message.

 

[Gasman]

On which line?

 

[arnelgp]

change it to:

If Len([DepartmentBookingOut] & "") = 0 Then
...
...

Or

IF [DepartmentBookingOut].ListIndex < 0 Then
...
...

 

[cheekybuddha]

@kevnaff,

Do you have Option Explicit declared at the top of every code module (above or below Option Compare Database)?

If not, add it to every module, and Debug menu -> Compile

It will highlight any undeclared variable errors in your code (one by one, you have to fix, repeat until there are no more errors.)

In the code above Message, Title and Response are undeclared

 

[arnelgp]

If [DepartmentBookingOut] Is Null Then

that can only be used in SQL statement or Domain function.

 

[cheekybuddha]

I have created a query called QueryPoolBookings with PoolBookings and ImportEquipment tables, and included all fields from PoolBookings and just the Location field from ImportEquipment. I have changed the form's record source to QueryPoolBookings.

Unless you have used an OUTER JOIN you will only ever show records where the CodeNo/[Code No] appears in both tables.

You will never see any records in your form where the CodeNo is in PoolBookings but not in ImportEquipment.