VBA Coding Related to Data Entry Form

[exlove]

Dear Al

Please find attached file and help me to resolve following issues.

username - admin
password

1) In Add Vehicle Form Contact Number Text Box should not allow value less than or greater than 10 Digits.
2) In Add New Vehicle Form Only Date Selection option should appear in following Text Boxes
Insurance date, MH Tax Date, NH Permit Date , Passing Date
3) When Date will be saved in Vehicle Sheet then it should be in DD-MM-YYYY Format only.
4) When Vehicle Number is entered only in Numeric Value then Update Vehicle code works fine,
But If Vehicle Number is entered in Numeric and Text Combined Value (MH09EM2727) then Update Vehicle code gives error.
5) I am unable to write code for Delete Button -
Expectations are as follow
If user will click on delete button then Delete Vehicle form will open
User will select the vehicle in combo box
and click on Delete Record button
It will ask "Do want to delete this vehicle permanently from database"
Yes No option
If user will click on Yes Button then message will come " Vehicle Deleted from the database "
If user will click on No Button then Dialog Box Screen will disappear and user will come on Delete form Screen.

Thanks in Advance
Atul

[Kenneth Hobson]

The reason you likely received no reply yet is because the file is fairly involved and you asked several questions. Simple examples and single questions usually get quicker replies.

DatePicker controls are fairly involved since there is no common and reliable date control. I added one version. It takes a bit of work to get it to do all that you wanted in (2) and (3). Run your userform like I did from the Module that I added. It shows your userform vbModeless and shows the calendar userform vbModeless. I added code to your userform to add dropdowns for the date textbox controls for calling the datepicker userform.

In your Insert button code, I just showed how to insert the calendar value and numberformat to [A1] to show how that is done. You can modify it to suit for Textbox4-8.

For (1), I added a Len()<>10 check rather than check for "".

For (4), you probably need to remove the CLng(). Rather than using a Match() routine, I would use a range Find routine.

I have not had time to look at (5). Here again, I would use a range Find method.

Anyway, this will give some things to tinker with...

[bakerman2]

For (4) Dim i as Variant, not as Integer.

Please Login or Register  to view this content.

For (5), on which form is the Delete button located ???

[exlove]

Dear Mr. Kenneth Hobson

May be I am wrong but I found that Add Form doesn't work
1) It wont allow to enter date
2) if we enter 1 then automatically old date is appeaing but when we try to change year then it doesn't change.
3) Form doesn't save data in Vehicle sheet.

You Said , "For (4), you probably need to remove the CLng(). Rather than using a Match() routine, I would use a range Find routine."
After doing that changes it doesn't work.

4) Thank you for adding Date Picker but it should work, Because when I click on Date Picker then it shows
error 401 stating - Cant show non-modal form when modal form is displayed

Thank you for your valuable help.
hope in future also you will solve the issue.

Atul

[exlove]

Dear Mr. bakerman2

1) I Tried using your code and happy that previous error has gone, but unfortunately next error is coming.
Request you to do the needful.
2) Even you could read my previous reply that date picker is also not working
3) Sorry I have uploaded wrong file without delete form. New file is uploaded with this post.

Thank you for your help.

Atul

[Kenneth Hobson]

In post #4:

1) and 2) Dates can be a tricky thing. Just because a string looks like a date does not mean that it is a date. In any case to work better, change the Change event to Exit for the 4 date textbox controls and add CDate(). e.g.

Please Login or Register  to view this content.

3) See:

In your Insert button code, I just showed how to insert the calendar value and numberformat to [A1] to show how that is done. You can modify it to suit for Textbox4-8.

Note that I put an Exit Sub after that part. As I instructed, delete those parts and use the concept that I illustrated for a value to a cell and then setting the numberformat for the date values in the cell.

4) See:

Run your userform like I did from the Module that I added. It shows your userform vbModeless and shows the calendar userform vbModeless.

[bakerman2]

Edited your Delete Vehicle form.

[exlove]

Dear Mr. bakerman2

Thank You for your help.
Issue is resolved.
Just want to add Yes No Button for confirmation of delete record.
Before deleting record it should ask "Do you really want to delete this record?"

Thank You Again

[bakerman2]

Here you go.

Please Login or Register  to view this content.

[exlove]

Dear Mr. Kenneth Hobson.

To be Frank I am not a coder.
But I copied codes from somewhere and got help from talented peoples like you.
I appreciate your efforts for this file but when I am trying to run this file following error is appearing.
Request you to resolve the same.

I request you please elaborate your point no. 4 step by step which actions I have to take so that the form can be run successfully.
Please give me working solution on Date selection function on 1 text Box, remaining I will do from my end.

Awaiting for your reply.

[exlove]

Dear All
No body has resolved my pending issue.
Does it mean that there is no answer on my query?

Awaiting for positive reply.

Atul

[dotchiejack]

Hi,
I have a look at your query tomorrow.

[exlove]

Dear Mr. bakerman2

Sorry for late response.
It gives following error.

Run-time error 1004
Delete method of Range class failed.


request you to resolve.

Another query

Is it possible to auto email that deleted record to specific email id after deletion?

Thanks in advance

[bakerman2]

Please Login or Register  to view this content.

[exlove]

Sir God Afternoon

Attached error is appearing while selecting vehicle number in vehicle update form.
MH09 EM 2727
or any vehicle number which is containing letters in it.

Please help

[dotchiejack]

Hi Atul,
A made an example.
First of all, it is 1 userform where you can add,edit en delete vehicles. (I don't know why you have separate userforms)
There is a calendar to fill the dates.
To edit or delete a vehicle,make your choice in the list.
When you delete a vehicle, a textbox is filled with vehicle info to mail.
You can search the vehicle list by Vehicle Number,Vehicle Owner or Contact Number
See attached.

[exlove]

The reason you likely received no reply yet is because the file is fairly involved and you asked several questions. Simple examples and single questions usually get quicker replies.

DatePicker controls are fairly involved since there is no common and reliable date control. I added one version. It takes a bit of work to get it to do all that you wanted in (2) and (3). Run your userform like I did from the Module that I added. It shows your userform vbModeless and shows the calendar userform vbModeless. I added code to your userform to add dropdowns for the date textbox controls for calling the datepicker userform.

In your Insert button code, I just showed how to insert the calendar value and numberformat to [A1] to show how that is done. You can modify it to suit for Textbox4-8.

For (1), I added a Len()<>10 check rather than check for "".

For (4), you probably need to remove the CLng(). Rather than using a Match() routine, I would use a range Find routine.

I have not had time to look at (5). Here again, I would use a range Find method.

Anyway, this will give some things to tinker with...

Dear Sir
As I explained you that I am not a coder
and I got the help from people like you
But I am not getting any solution from you about attached screenshot.
even about Point No. 4 request you to give solution.

I know that you can solve this issue.
Please help

atul

[exlove]

Dear Sir
As I explained you that I am not a coder
and I got the help from people like you
But I am not getting any solution from you about attached screenshot.
even about Point No. 4 request you to give solution.

I know that you can solve this issue.
Please help

atul

Sir I request you to just insert the code for Test box 4 as a sample
So I can understand how to insert code for other text boxes

Thanks in advance
Atul

[exlove]

Hi Atul,
A made an example.
First of all, it is 1 userform where you can add,edit en delete vehicles. (I don't know why you have separate userforms)
There is a calendar to fill the dates.
To edit or delete a vehicle,make your choice in the list.
When you delete a vehicle, a textbox is filled with vehicle info to mail.
You can search the vehicle list by Vehicle Number,Vehicle Owner or Contact Number
See attached.

I really apologize to say that I skipped your reply.
Just now I saw your reply and I am happy to say that you have done excellent job for me.
Reason behind making separate form is I don't want to give access for deletion or edit to my junior. Username and password is admin
I am not a coder but I got the help from people like you and there is provision to give access user wise in my attached sheet.
So I want to add other masters in this sheet like Driver Master, Destination Master, Trip Expense Master etc.
So everyone will not have access for all sheets. So I made multiple forms.

Now After Making Multiple Forms Date Facility is working only in 1 Form (Driver Form) Because I have make changes in Cal Module.
Please help in this regard
Because I have to insert many more forms in this file. and it should work in each form.
If possible make first 3 field mandatory and contact no. field should be 10 digit not more or less

Thank you for our excellent help.