Runtime error 13 type mismatch

[ssanfu]

June answered your follow up questions, but I wanted to add a comment.



I would have the code in the FORM Before update event. That way the job numbers is not generated until just before a save. You might have to experiment to see if the Form Before Update events fires if you cancel the add/edit of the record.

You would also have to add code to check if there is already a value in the "JobNumber" field and NOT update the field if there is a value. Otherwise the job number could be inadvertently changed.

[ShaunG]

Yes, but then you need to commit the record immediately so another user will not create the same ID. This means if you cancel the input you will have an incomplete record in table or delete record and have gap in sequence. In my db I could not allow gap - every ID had to be accounted for. I have code in Cancel button that reverses user inputs and saves this 'blank' record. Then when another user starts a record, code first searches for blank record and opens it for user input. If no blank found then new ID and record created. I also have a void procedure so established records that we decide do not need to be further processed will be reported as VOIDED. DB is to report construction lab sample testing data.

i had the same error with this code for some reason... maybe i've done some thing wrong some where.

in my head ive always thought if i could keep the records in the tables to a minimum is better is this correct or doesnt really matter?

I got my JobNumbers working by the following way - ive added 2 new fields, 1 I made called JobPrefix and set default ="CT" and the other JobNextNumber with the Dmax code in it, the JobNumber field is still present in table as i use it in my query but nothing is actually saved in this field in the table. in a query i combine the JobPrefix and JobNextNumber in the JobNumber field which gives me the Job Number Im looking for e.g. J2365
this works as i want but it i may have the issue you have suggested June7 of another user creating a same JobNumber if done at exact same time, im not sure and will have to test.


for my Orders ive tried to do the same thing as i've done in my Jobs but i've got an error. ive added 2 new fields and 1 I made called OrderPrefix and set default ="CT" and the other OrderNextNumber with the Dmax code
then i'm using the JobNumber field in my query to combine the 2 fields as i've done in with my Jobs.....

when i try to save and exit im receiving an error that i cannot add or change a record because a related record is required in the table "tblOrderNumbers"

with orders form i'm not sure how i did it but if someone is creating an order and if someone else trys, it locks them out so i don't think the same issue would occur here with duplicate Numbers.

[ShaunG]

June answered your follow up questions, but I wanted to add a comment.

I would have the code in the FORM Before update event. That way the job numbers is not generated until just before a save. You might have to experiment to see if the Form Before Update events fires if you cancel the add/edit of the record.

You would also have to add code to check if there is already a value in the "JobNumber" field and NOT update the field if there is a value. Otherwise the job number could be inadvertently changed.

thanks ssanfu i never thought ive putting in FORM before update event I like that idea, just not sure if it will work in my db, i managed to get it working as i've explained above in my reply to june7 how i went about about it, i tried doing same way to my orders but have an issue im working on as explained above as well.

[ssanfu]

Any chance you would post your dB? Look at the bottom of June's post for instructions.

[ShaunG]

Any chance you would post your dB? Look at the bottom of June's post for instructions.

No worries ssanfu ill do it now

[ShaunG]

Any chance you would post your dB? Look at the bottom of June's post for instructions.

here it is....

to login SELECT Either Shaun G or default user and password is the number - 0

i have changed the relevant controls Buttons to white -

to see what ive done with the Job Number - click Admin, New Job Entry and this loads the form

to create a new Order after logging in - click Products Not ordered, then select the products to order and click open selected products which opens order form, click Generate to find next order number and then reselect the products which confirms also assigns the order number to product.

i have it setup so when Order Number is generated and a product is confirmed a control process order = true which is used for another filter.

to open a job after logging in select Jobs, the last job will be select click view job progress.

there is still a couple bugs and is a bit messy my tblOrders is not being used and will be deleted

[ShaunG]

hopefully instructions easy to understand

[ShaunG]

I have worked why my OrderNumber was not recording.... was copying the wrong control to my subform control i wasnt copying the PK - FK Records.

all works as it should now, i have cleaned up the db as well. Ill upload the working copy when im home

June7 you were helping me in another thread regarding my ordering method.... i was going to upload db but i needed to change a few things and clean it up before uploading.

[MattBaldry]

Just for my input, which is not the greatest, but I have written ID's like this where the order number is only entered when the order is saved. I have an AutoNumber as the main PK and then order number as another PK, but this is only entered when the order is saved. Sage Accounts also works in the same way. We can have 5 users all enter a PO at the same time but the next order number is allocated to the first person to click save, and only when you click save. If the order is not saved, everything is undone and the incomplete record is deleted and leaves your order number sequence as it was. The AutoNumber key may be missing a number, but as this is not used as the order number it does not matter.

~Matt

[ShaunG]

Just for my input, which is not the greatest, but I have written ID's like this where the order number is only entered when the order is saved. I have an AutoNumber as the main PK and then order number as another PK, but this is only entered when the order is saved. Sage Accounts also works in the same way. We can have 5 users all enter a PO at the same time but the next order number is allocated to the first person to click save, and only when you click save. If the order is not saved, everything is undone and the incomplete record is deleted and leaves your order number sequence as it was. The AutoNumber key may be missing a number, but as this is not used as the order number it does not matter.

~Matt

i do like the this idea, june7 and ssanfu also have mentioned it...

did you achieve this through code or just the way db was setup?

ill be posting my db soon when i get a chance to clean sensitive info

[MattBaldry]

i do like the this idea, june7 and ssanfu also have mentioned it...

did you achieve this through code or just the way db was setup?

ill be posting my db soon when i get a chance to clean sensitive info

Shaun,

I use a VBA code attached to a save button. My purchase orders have the prefix PO, and sales orders the very original SO. I do not have this as part of the number itself like you do. This prefix only shows in the text box and on the order. The actual data is just a number, IE 1501, but when displayed on the PO form, it appears as PO1501. It is much easier to do this way rather than than have a text field with the prefix on it. The data itself does not need the prefix, and it is much easier to add +1 to a number field than a mixed format text field.

~Matt

[ShaunG]

Shaun,

I use a VBA code attached to a save button. My purchase orders have the prefix PO, and sales orders the very original SO. I do not have this as part of the number itself like you do. This prefix only shows in the text box and on the order. The actual data is just a number, IE 1501, but when displayed on the PO form, it appears as PO1501. It is much easier to do this way rather than than have a text field with the prefix on it. The data itself does not need the prefix, and it is much easier to add +1 to a number field than a mixed format text field.

~Matt

I cant upload the db for some reason my winzip on home comp is not working properly, ill upload when in the office tomorrow morning, have a look see what you think.

[June7]

Instead of WinZip, can't you use Windows Compression?

Yes, creating ID only when record is saved is simple. However, if user must see the ID during data entry, gets complicated in a multi-user db. In our case, the ID must be written on sample submittal form.

[ShaunG]

Instead of WinZip, can't you use Windows Compression?

Yes, creating ID only when record is saved is simple. However, if user must see the ID during data entry, gets complicated in a multi-user db. In our case, the ID must be written on sample submittal form.

hopefully that worked June7 i tried the compression but I've never used it before.

yeah i have said before the user being able to see the Order Number is beneficial as some place's need an order number before they can give there stock details. i don't know why it doesn't sound right to me but just the way some operate.



i forgot to white out buttons use the trial2 db, and password = 0

thanks.

[ssanfu]

i forgot to white out buttons use the trial2 db

There is no "Trial 2" dB, only "Trial 1" (in both zip files).