Run time Error 3075

[namu]

Hi,

First, i have a main form (MaterialRequest) bound on "tblMaterialRequest" with a subform(MaterialRequestDetails) which is bound to "tblMaterialRequestDetals" and "tblItemCodes" .

What i want to do is i want to have an autonumbering on the LineNumber field. The code i used on the Before Insert event is :

Me.LineNumber = Nz(DMax("LineNumber", "tblMaterialRequestDetails", "MaterialRequestID = " & Me.MaterialRequestID), 0) + 1

The problem i am having is, it is always giving me a runtime error 3075 : Syntax error (missing operator) in query expression 'MaterialRequestID ='.

MaterialRequestID is a number field.

 

[MarkK]

Looks like the field Me.MaterialRequestID has no value. Test the value before you run the DMax(), like . . .

[COLOR="Red"]MsgBox "MaterialRequestID: " & me.MaterialRequestID[/COLOR]
Me.LineNumber = Nz(DMax("LineNumber", "tblMaterialRequestDetails", "MaterialRequestID = " & Me.MaterialRequestID), 0) + 1

. . . and see if it actually is a number on the form . .

 

[namu]

Hi, Thanks for input. You are right. At this point the MaterialRequestID has no value. MaterialRequestID is the FK in my subform (tblMaterialRequestDetails). How will i proceed after this?

I just noticed that the record source of my subform is not tblMaterialRequestDetails anymore. It was changed to a Select query because i added the tblItemCode to link it to the tblMaterialRequestDetails to auto fill the item description.

SELECT tblMaterialRequestDetails.ID, tblMaterialRequestDetails.MaterialRequestID, tblMaterialRequestDetails.LineNumber, tblMaterialRequestDetails.ItemCodeID, tblMaterialRequestDetails.Quantity, tblMaterialRequestDetails.UoM, tblItemCode.ItemDescription FROM tblItemCode INNER JOIN tblMaterialRequestDetails ON tblItemCode.ID = tblMaterialRequestDetails.ItemCodeID;

 

[namu]

i attached a copy of my testdb for further checking.

View attachment TestDB.accdb

 

[arnelgp]

test this please.

 

[MarkK]

The trick is that you put a ComboBox on the subform on the ItemCodeID field. Then in the combo you look up the ID and ItemDescription fields from tblItemCode, and then the ItemCode field in the RequestDetails table is set directly by the value (the bound column) of the combo, but the user sees the description.

To do this you need to be concerned with these properties of the combo,
1) RowSource, should get data from tblItemCode (SELECT ID, ItemDescription FROM tblItemCode ORDER BY ItemDescription)
2) RowSourceType: table/query
3) ControlSource: ItemCodeID, from the recordsource of the subform, tblMaterialRequestDetails
4) ColumnCount: 2
5) ColumnWidths: 0";2" (this hides the first numeric key field column, so the user sees and selects the ItemDescription, not the ID.)
6) BoundColumn: 1

And set the subform's recordsource back to the just the RequestDetails, don't tie in the other table, ItemCode, let the Combo do that.

Hope this helps,

 

[MarkK]

Or get arnel to do it for you!
Cheers,

 

[namu]

Hello Arnel,

i tested the sample db that you sent, however the LineNumber is not incrementing. It remain as number 1 for all the records added. I slightly modified the code you added on the before insert event to something like :

Me.LineNumber = Nz(DMax("LineNumber", "tblMaterialRequestDetails", "MaterialRequestID = " & Nz(Me.MaterialRequestID, 0) + 1), 0) + 1

but still not solve my problem.

Hello Mark,

In reality I have approximately 130,000 itemcodes. The idea of combobox may not be convenient for the users. I wish to open a separate item code list for their selection. Everything works fine and the only problem is the LineNumber auto numbering.

Well, Thank you very much both for your valuable inputs. I really appreciate your help. I guess i'll just stick on the ItemCodeID after update event procedure where i just need to dlookup on the tblItemCode to fill in the ItemDescription. This way, i can separate the tblItemCodes and the tblMaterialRequestDetails.

Cheers!

 

[arnelgp]

sorry about that mr.namu, try this this time using Recordset of your subform.

 

[namu]

Hello Arnel,

It works just fine on the first record. However, when i try to add data on the next record of MaterialRequest form, it gives me a run time error 3021. No current record. Do you think this can be applied on the next record as well? Thank you for your help.

Kind regards,

namu

 

[arnelgp]

try again please.

 

[namu]

Thank you so much Mr. Arnel. You saved me again! Cheers!