Hello,
I am having a complex issue that I don't even know how to name - therefore a bit confusing title of the topic.
I keep information about my products' serial numbers. The serial number is first entered when I receive the product from my supplier. Then it is inserted again when I am creating a sales order. The database is able to link these 2 serial number inputs and it knows that it is the same unit and it pairs the supplier and the customer.
It works like a charm, but some products have no serial numbers. So I am storing them with dummy serial numbers (for example FICTIONAL_00001, FICTIONAL_00002 and so on). Right now I need to look in my OrderDetails Table for the last number used and manually calculate the iteration. I got annoyed of this, so I am trying to automate this at the moment.
Another problem is that I want to keep information about how many pieces of a product do I have in stock. So in case of the dummy products, when I am creating a sales order, I need to choose a dummy S/N that has been assigned to this product type, so it removes itself from the storage.
So far I managed to accomplish this:
- When I am doing a purchase order and enter "f" into the serial number field (stands for fictional), an After Update code runs, looks into the Order Details Table, calculates the correct dummy number (Format and Dcount functions), sets it into the field and sets focus on the next one (that's 100 % perfect result that I'm looking for)
Now the complications begin when I am trying to do the sales order part. In human language, I want this to happen:
I input "f" that says that I am selling a product that has no S/N. The form jumps to the Product Type combo box where I choose the product type (already done and working). After I choose the product type (After Update event), I need the database to look into the Order Details Table, pull information about products in stock filtered by the selected product type, select the earliest dummy S/N and set it to the form text box.
For example the query result would be:
Product SerialNumber Item 1 FICTIONAL_00010 Item 1 FICTIONAL_00015 Item 1 FICTIONAL_00017 Item 1 FICTIONAL_00033
Now, I would want FICTIONAL_00010 to be inserted in my form control.
I tried some coding that would run the query with a parameter based on the product type that I choose but no good results so far. And also I have no idea how to choose the earliest dummy S/N and actually insert it to a text box.
Any advice would be greatly appreciated!
Tomas