how to look in cases and if else providing it does not break

[tariq nawaz]

input_Winwick_form1.zip
i am a novice to the databases access and VBA code.
i have done a small application .


can someone give me any suggestions how i can improve the code giving suggestions.
highly highly appreciated for that in advance.

Regards

A learner

[rpeare]

If you're looking for analysis do you have a specific question, without some explanation as to what this database is supposed to do or how you want/wanted things to be someone could spend hours looking it over. For instance if this database is for tracking shipping of packages what is your table 'all_services' supposed to be for? Some of the records only have a value in one field. What is your table 'main' supposed to be it looks like you have a lot of patchy data, I don't know whether that's by design or you deleted stuff to protect privacy. I don't know how you are arranging data in 'main' either. If it's by design it looks like you need to consider normalizing this data a little more. For instance what if a customer ships multiple packages and you want to display those all on the same bill I don't know how you'd do that with your current design other than looking at the date, but what if someone ships 3 packages in the morning and 2 in the evening. There are a bunch of questions that can come from a real in depth analysis.

[tariq nawaz]

If you're looking for analysis do you have a specific question, without some explanation as to what this database is supposed to do or how you want/wanted things to be someone could spend hours looking it over. For instance if this database is for tracking shipping of packages what is your table 'all_services' supposed to be for? Some of the records only have a value in one field. What is your table 'main' supposed to be it looks like you have a lot of patchy data, I don't know whether that's by design or you deleted stuff to protect privacy. I don't know how you are arranging data in 'main' either. If it's by design it looks like you need to consider normalizing this data a little more. For instance what if a customer ships multiple packages and you want to display those all on the same bill I don't know how you'd do that with your current design other than looking at the date, but what if someone ships 3 packages in the morning and 2 in the evening. There are a bunch of questions that can come from a real in depth analysis.

thanks a lot for looking into it.
i am just taking the values for my combo box from all services and auto fill UK when destination is UK using combo box.
all vendors have different vendor numbers so they are assigned randomly in main so that's why there is a lot of entries blank in main.
i need to export the table at the end of day.so it does not matter how the records come in eve or morning.
if you have gone that deep can you please answer my few questions. it will be highly appreciated.

as it is my first application and don't know much but i have put a lot of effort from lerarning Access to VBA.

1. how can i improve VBA code in background of input table as i want some logical way that no one can not save until all the values are in.
2. i can have better way how to export data. it is already doing export but it gives blank lines loads of on start as i export.

Regards

A learner

[rpeare]

1. You are not 'saving' your data in a bound form. Any time new data is entered it's automatically on your table. Your 'save' button isn't doing anything other than moving to a new record. If you want to validate that a piece of data exists you'd have to do that with code and I have found that using unbound controls makes that part a lot easier. In your case you have a couple of options 1. on each field in your form that is 'required' you can have a validation rule testing whether or not it's null and if it has a valid value (if it's not selecting from a combobox that's limited to a specific set of items) 2. You can do a validation when you click your 'save' button so that the user can not move away from the record until they fix their errors. With either option you have to handle situations where someone does not complete a record but closes or exits your form as well.
2. The reason your report gives you a lot of blanks is that your table is very patchy. Open up the table you're reporting from, it's got very erratic data which is why I mentioned normalizing. I do not know your situation but if every record is supposed to have *something* in every field you are not preventing users from navigating away from those records if they have left something out.

[tariq nawaz]

ignore my bound and unbound form question as i have already sorted that.i made them unbound and then using VBA update them in the table. thanks this forum and you guys.
still have issues with how to validate that all the values are there.
actually i have different options and depending on them some of my fields will be blank.
i have table with vendor_name, vendor_number , service, weight, detination, end_cust_name, postcode, tracking_number, zone, region
so for exmple if service=BFPO_Packet then i need that these fields should be there before saving: vendor_name, vendor_number , service, weight
if service =Airsure then i need then i need that these fields should be there before saving:vendor_name, vendor_number , service, weight, detination, tracking_number, zone, region

i am thinking for having some code in save button and if statement that if service is airsure and these fields are not null enable to save.

will be highly appreciated if you guys can give me exact code which i can use in save button background.

Regards

A learner

[rpeare]

We can't give you 'exact' code, we don't know what your rules are. If you are using an UNBOUND form the first rule you mentioned would go something like this in the ON CLICK event of your save button:

If fldService = "BFPO_Packet" AND (isnull(vendor_name) OR isnull(vendor_number) OR isnull(service) OR isnull(weight)) Then
     Msgbox "Cannot add record, BFPO Packets require vendor name, number, service and weight to be filled out"
else (next error check)
etc.
endif

[tariq nawaz]

We can't give you 'exact' code, we don't know what your rules are. If you are using an UNBOUND form the first rule you mentioned would go something like this in the ON CLICK event of your save button:

If fldService = "BFPO_Packet" AND (isnull(vendor_name) OR isnull(vendor_number) OR isnull(service) OR isnull(weight)) Then
     Msgbox "Cannot add record, BFPO Packets require vendor name, number, service and weight to be filled out"
else (next error check)
etc.
endif

sorry mate may be i am missing something but this ISNULL condition does not worked for me.
i have used if (me.vendor_name="" or me.vendor_number="" or me.weight="" or me.service="")
but there is a small problem. if someone wipes the data after entering it it will still let enter save the record.
for example if someone came in weight box entered something and wiped the data before saving it will still allow to enter the record.

is there any other function than isNULL or is there anyway i can use me..."" condition in a better way.

Much apreciated for your help guys

Regards

A learner