Dlookup As Control Source Or Default Value? Actual Quantities for Inventory Database

[lisarisa333]

I am soooo happy to report that I got it working today!!!! Absolutely could not have done it without you! Thanks Vlad!!!



I have a few other things to take care of before I call it done, but I am going to try to get those done on my own. Will post back if I need more of your expertise!!

Thanks again,
Lisa

[Gicu]

Glad to hear Lisa, good luck with your project!

Cheers,
Vlad

[lisarisa333]

Hi Vlad,

I am having trouble getting a DMax code to give me the result I need. Or any result at all actually. Here's the process:

If a new tag number is entered in the receive form, then "order" will equal "1". If it is an existing tag number, then "order" will equal DMax + 1. I tried this in the after update event of cboTagID, after the DLookup codes you helped with, and also the after update event of txtTagNumber.

1st tried - Me.txtOrder = Nz(DMax("[Order]", "TransactionDetail", "[TagID] = '" & Me.[cboTagID] & " ' "), 0) + 1

Then - Me.txtOrder = Nz(DMax("[Order]", "TransactionDetail", "[TagID] = " & Me.[cboTagID]), 0) + 1

I'm not getting an error or anything, just a blank.

Any ideas?

[Gicu]

Hi Lisa,
This is what I got and it seems to be working:
[CODE]
Private Sub cboTagID_AfterUpdate()
'vlad June28,2019
If Not IsNull(Me.cboTagID) And Me.Parent.Form.Controls("TypeID") <> 1 Then
'populate the related info from the received (type=1) transaction
Me.txtUOM = DLookup("[TransQtyUOM]", "[qryReceivedTransactionsInfo]", "[TagID] = " & Me.cboTagID)
Me.txtTransCountUOM = DLookup("[TransCountUOM]", "[qryReceivedTransactionsInfo]", "[TagID] =" & Me.cboTagID)
End If
Me.Order = Nz(DMax("[Order]", "TransactionDetail", "[TagID] = " & Me.[cboTagID]), 0) + 1
End Sub
[CODE]

Cheers<
Vlad

[lisarisa333]

Great! It works!! I didn't have the "End If" in there. Now let me ask you, is it possible to add something else to the cboTagID_AfterUpdate event?

On the issue subform, cboTagID has it's row source set to a query that only displays the part tags for the part number. I'm trying to get that to happen on the receive_subform, but only for the RTS transaction.

[lisarisa333]

Hey Vlad,

Wondering if you had a chance to see my last post / question.

[Gicu]

Sorry Lisa, got caught with some other stuff, here you go.

Cheers,
Vlad

[lisarisa333]

Amazing Vlad. Your knowledge is impressive! How long have you worked with Access?

So my database is coming along nicely. Or so I thought. I was entering some test data and came across an issue.

Initially the format was number, double. I was able to enter 17 as the actual issued, and then the calculation made the qty 1.1333333333. I changed the format to currency based on some googling I did. And now I have this mess.



The entries under Actual - 13.05 was entered as 13, 16.95 was entered as 17. But I think the way the VBA is set up, it makes that unbound text box = the bound text box which is the calculated query field. Do you know how I can force it?

Do you have any ideas or suggestions for the best way to handle this? Go back to number? Keep at currency? I'm at a standstill now. Just when you think everything is perfect, BAM! LoL.

[Gicu]

Not sure I fully follow you, as I don't see the currency format on my sample but I would keep it as number and apply a Fixed format on the quantity text box on the forms - under the format property choose Fixed and and the decimal choose 2 - that will make the calculated value 1.13.

Cheers,
Vlad

[lisarisa333]

I did as you said on the first quantity text box, and left the others as is. I can't say that it is calculating incorrectly because 1.13 x 15 = 16.95. But I need that actual to be 17.

[Gicu]

But it is a calculated field, don't get why you would have a 1.13 quantity and a count of 15 each. In any way to force it you probably need to use Round in your query (TransactionDetailQuery):

Actual Quantity: Round([TransQty]*[TransCount],0)

Cheers,
Vlad

[lisarisa333]

I'm sure I'm not explaining my thought process properly. I'm sorry.

We need to RTS 17 each. That is from the actual quantity.

Quantity | Q-UOM | Count | C-UOM | Actual | A-UOM
1.13333 pks 15 each 17 each

17 actual / 15 count = 1.133333333333333 quantity
17 each / 15 each(in a pack) = 1.133333333333333 packs

So I definitely need the Actual to stay 17, but have the Quantity round......if possible.

Hope I've explained it a bit better.

I'm now wondering if I need to create another query for all non-received transactions.

[lisarisa333]

Well I think I've got it!! I thought I went far enough back on a previous version that there was no Rounding in the VBA code, but I found some, removed it, and it works like a charm!!