DLookup Can't assign a value Error

[steve21nj]

I am doing something similar to a library system, except it is property. For this you scan the bar code which returns a number.
I am running into an error on my last item PropertyID = Nz.....
It produces a run-time error, you can't assign a value to this object.
I am confused because the DetentionID passed with no issue but PropertyID doesn't, it simply assigns a new ID.

[SIZE=3][FONT=Arial]Option Compare Database[/FONT][/SIZE]
 
[SIZE=3][FONT=Arial]Private Sub Bag__AfterUpdate()[/FONT][/SIZE]
 
[SIZE=3][FONT=Arial]DateIn = Nz(DLookup("DateIn", "tblPropertyDetails", "[BagNum] = '" & [BagNum] & "'"))[/FONT][/SIZE]
[SIZE=3][FONT=Arial]PropertyTypeID = Nz(DLookup("PropertyTypeID", "tblPropertyDetails", "[BagNum] = '" & [BagNum] & "'"))[/FONT][/SIZE]
[SIZE=3][FONT=Arial]Notes = Nz(DLookup("Notes", "tblPropertyDetails", "[BagNum] = '" & [BagNum] & "'"))[/FONT][/SIZE]
[SIZE=3][FONT=Arial]TransferFrom = Nz(DLookup("TransferFrom", "tblPropertyDetails", "[BagNum] = '" & [BagNum] & "'"))[/FONT][/SIZE]
[SIZE=3][FONT=Arial]PropertyDestroyed = Nz(DLookup("PropertyDestroyed", "tblPropertyDetails", "[BagNum] = '" & [BagNum] & "'"))[/FONT][/SIZE]
[SIZE=3][FONT=Arial]DetentionID = Nz(DLookup("DetentionID", "tblPropertyDetails", "[BagNum] = '" & [BagNum] & "'"))[/FONT][/SIZE]
[SIZE=3][FONT=Arial][COLOR=royalblue]PropertyID = Nz(DLookup("PropertyID", "tblPropertyDetails", "[BagNum] = '" & [BagNum] & "'"))[/COLOR][/FONT][/SIZE]
 
[SIZE=3][FONT=Arial]End Sub[/FONT][/SIZE]

Any suggestions on why or improving this code?

 

[spikepl]

Option Explicit is missing from the top of your code

PropertyID is probably a control with some expression assigned in the Control Source property

 

[steve21nj]

I attached a snap shot of the control source of PropertyID. It doesn't have an expression behind it. I tried adding Option Explicit, then adding Me.

Option Compare Database
Option Explicit
 
Private Sub Bag__AfterUpdate()
 
Me.DateIn = Nz(DLookup("DateIn", "tblPropertyDetails", "[BagNum] = '" & [BagNum] & "'"))
Me.PropertyTypeID = Nz(DLookup("PropertyTypeID", "tblPropertyDetails", "[BagNum] = '" & [BagNum] & "'"))
Me.Notes = Nz(DLookup("Notes", "tblPropertyDetails", "[BagNum] = '" & [BagNum] & "'"))
Me.TransferFrom = Nz(DLookup("TransferFrom", "tblPropertyDetails", "[BagNum] = '" & [BagNum] & "'"))
Me.PropertyDestroyed = Nz(DLookup("PropertyDestroyed", "tblPropertyDetails", "[BagNum] = '" & [BagNum] & "'"))
Me.DetentionID = Nz(DLookup("DetentionID", "tblPropertyDetails", "[BagNum] = '" & [BagNum] & "'"))
Me.PropertyID = Nz(DLookup("PropertyID", "tblPropertyDetails", "[BagNum] = '" & [BagNum] & "'"))
 
End Sub

Any other suggestions?

 

[steve21nj]

I am still having issues with the last DLookup. Instead of returning the value, it pulls a new record value. For example if the DLookup value is 42, it returns 75 (or whatever auto-number is next). The previous two values return fine.

Me.PropertyID = Nz(DLookup("PropertyID", "tblPropertyDetails", "[BagNum] = '" & [BagNum] & "'"))

 

[TheSmileyCoder]

The problem is that the field PropertyID is an autonumber field. You can't manually assign values to this field.

If the bag number is what your bar code scanner returns, and the bag number is already in the database, it sounds to me like what you want to do, is navigate to the already existing record.
Me.RecordSet.FindFirst "[BagNum] = '" & [BagNum] & "'"

If bagNum is a Number type field and NOT a text field, you can loose the single quotes
Me.RecordSet.FindFirst "[BagNum] = " & [BagNum]


Please confirm what your intentions are with the scanned number in relation to your existing data. Do you want to navigate to existing record? Append data to existing? Create a new record?

 

[steve21nj]

Make sense about the autonumber field. How would I get the value of the PropertyID to fill correctly then? I am using my form to update this and several other records by a continuous sub form.

The first field, BagNum, has an after update event with the code from post #3 behind it.

Where would I place this code? Would I fit it in with the code from post #3? Or completely remove that code?

[COLOR=black][FONT=Verdana]Me.RecordSet.FindFirst "[BagNum] = '" & [BagNum] & "'"[/FONT][/COLOR]

 

[TheSmileyCoder]

Please confirm what your intentions are with the scanned number in relation to your existing data. Do you want to navigate to existing record? Append data to existing? Create a new record?

I need you to answer the questions posted for you.

 

[steve21nj]

If the bag number is what your bar code scanner returns, and the bag number is already in the database, it sounds to me like what you want to do, is navigate to the already existing record.
Me.RecordSet.FindFirst "[BagNum] = '" & [BagNum] & "'"

Yes I want to navigate to the existing record, after update.
At the same time I want scan several bags to also view their records on the same subform.

If bagNum is a Number type field and NOT a text field, you can loose the single quotes
Me.RecordSet.FindFirst "[BagNum] = " & [BagNum]

The bag number is a text field.

Please confirm what your intentions are with the scanned number in relation to your existing data. Do you want to navigate to existing record? Append data to existing? Create a new record?

I want to update the existing record from my main form. I'd like the data to be time stamped after update. The idea is to track property coming in and out. At any given time, we could have 50 bags that need to be scanned out.

I still dont understand why I can't return the autonumbers' value if it has already been assigned.

 

[steve21nj]

I was able to assign the values correctly, my error pointed me back to the indexing of certain fields that were non autonumbers.

With that said, when I scan the bag number, and the after update VBA takes place, it writes the values to the table (duplicating the record). So I then have two identical records in the same table. The original and the updated. Any way of preventing that?

 

[TheSmileyCoder]

I still seem to lack a basic understanding of the intended logic behind the form and the data, otherwise my responses will at best be misleading, at worst directly wrong.

As I understand it now, you scan some item, and based on the scan want to make modifications to records in the db. Without understanding your data structure its hard to advice. Can a item be design only be scanned once for example? Then we are talking about updating some field in the main record saying DateReceived=Now Where BagNum=your scanned bag num.

If an item can be scanned multiple times what we really need to do is modify a sub record related to your main record with information such as the action being taken, examples:
BagNum XXXX
Action: Sent
Date: 2012-02-02

BagNum XXXX
Action Received
Date: 2012-03-03

BagNum XXXX
Action: Sent
Date: 2012-04-04


The approach to be used is quite different based on the business logic. You describe it as a library system so I am guessing we are leaning more towards the latter described approach.

In that case lets imagine your have a form bound to your table of bags. In that form, add a header, add a textbox txtScanBox to the header, and possibly disable all other control. Add a subform showing loan details. When a item is scanned you want to:
A) Form to navigate to the bag.
B) Input a record into tblLoanDetails saying the item was received/Sent/other?
C) Refresh subform to show the fresh information.

Now the unbound textbox AfterUpdate event could look like so:

Private Sub txtScanBox_AfterUpdate()
  'Navigate to form
  Me.Recordset.FindFirst="[BagNum] = '" & me.txtScanBox & "'"
  If Me.Recordset.Nomatch then
    MsgBox "Error, bag number not found"
    Exit Sub
  End If

  'add loan details
  Currentdb.Execute "INSERT INTO tblLoanDetails ( BagNum, Action, ActionDate ) " & _
                            "SELECT '" & me.txtScanBox & "', 'Received', Date();
  
  'Refresh subform showing loan details
  Me.SubFormName.Requery
End Sub