Need Help on Basic settings!

[Nagarjun]

Hi
My query is pertaining to the call tracker template. Since I'm an absolute novice with computers ad have no Knowledge of MS-Access whatsoever I needed help to understand the template better!(PS I'm unaware of SQL or any technologies since I'm a Mechanical Engineering student). We are doing a project at a company that functions on calls. I need help to understand MS-Access better only with regard to this template! I'm really sorry people.. You may laugh at the fact that i have no understanding of Access but i DESPERATELY NEED YOUR HELP! I did try MS's office help.. It isn't exactly too simple or its just that i am too dumb!

My queries are as follows:

1)The ID generation of the template is great, but it can't be edited so if there is any mistake in the form entry.. that value is gone for a toss and the next number comes into play, so one value got skipped!
2)Is it possible to manipulate the field values such that there is a prefix.. alpha-numeric values come into use rather than just numbers?
3)Can anyone let me know how i can absorb existing information from a excel table and integrate only the data into the access forms/tables?
4)If for one come company there are multiple contract numbers existing and i need to choose a contract number from the form drop down box, how can i limit the values from all contract numbers to only the relevant ones of that particular company?
5)Is there any way in which i can lock down the form so that there is only data selection rather than entry? The company would prefer it that way because employees there do tend to make a hash of things!

Thats it for now! I'm really sorry since this is my first query, I may have asked very silly questions! But I'm a quick learner and desperately need this thing to start working for me! I'd really appreciate any help on any of the questions!!

Thanks in advance!

[June7]

1. Gaps in autonumber sequence should not be an issue. This field type is intended for use by Access and users do not even need to be aware of it.

2. Not with an autonumber field type.

3. Access can link to or import from Excel spreadsheet.

4. This is cascading (dependent) combo/list boxes. Review tutorials at http://datapigtechnologies.com/AccessMain.htm especially the 3 on comboboxes in the Access Forms: Control Basics section.

5. This can be controlled with properties of the form. Set AllowAdditions, AllowEdits, AllowDeletions to no. Review tutorial Build a Custom Filter in Your form in the Access Forms: Tips and Techniques section of the referenced site.

[Nagarjun]

Hey JUNE7!!! Thank you so much for these answers!! Really helped! However I just had a few more queries.. Hope you don't mind! #lifesaver#
1) Is it possible that access data be made available online to be shared? Can this be accomplished using sharepoint or is it possible for real time updating on any particular website?
2)Say i want to delete a autonumber entry in access, I heard that there is a code that needs to be written! do you by any chance happen to have the code?

Thank you so much for the reply once again!!

[June7]

1. Can be done. Sharepoint is one option. Asp.net might be another. I haven't built web database.

2. What do you mean? Deleting a record having an autonumber ID is not any different than deleting any record.

[Nagarjun]

how do i accomplish it using Asp.net?

2.Deleting an entry will block the id number. Say entry 7 was wrongly entered and deleted.. then the next entry will be 8 and not 7.. Can this be rectified

3 Is it possible to have 2 autonumber fields in the same table? It isn't allwing me to do so! In that case.. can u help me with the code to ensure that the number is incremented on each entry?

[June7]

Asp.net is a web design program. I don't know coding for dynamic data web pages. Since this is not an Asp or Sharepoint forum, I suggest you explore elsewhere.
http://www.asp.net/mvc
http://forums.aspfree.com/

Once the autonumber is initiated it is used up, delete or abort record, it is gone.

One autonumber field.

Coding for generating unique ID is common topic. This can get tricky, especially if there are multiple users. You could have code in the OnCurrent event of form, like:

If IsNull(Me!ID) Then Me!ID = DMax("ID", "tablename") + 1

[Nagarjun]

I cannot have a the same name for 2 fields right? So I have to give a different name to the actual ID, In that case say i want it to mimic the actions of the original ID field, is that the code I use, If so where? and the increments occur by themselves?

Is there any way I can mail you the draft copy of the template we have edited and you could let me know how to make these modifications? I can show you the template and the editing we have done to it..

[June7]

Can attach files to post. Follow instructions at bottom of my post.

I use only VBA, not macros. The code I suggest would be in an event procedure, I suggested the form OnCurrent event.

Review this http://office.microsoft.com/en-us/ac...010341717.aspx

[Nagarjun]

I'll attach first thing in 12 hrs... Sorry about that..Just need to rework on it.. I'll attach what the company does as well is that ok with you?

Thank you so much June7!!

[Nagarjun]

template.zip

Hello June7! I have attached the template along with some info you might need in a word doc!! Seriously I can't thank you enough for your help! But I sincerely offer my deepest gratitude for your taking it upon yourself to help people/ (access illiterates) like me!! Thank you so much!

[June7]

1. Datasheet view for the call log is not what I would prefer. Scrolling to see all fields is annoying. Could use Continuous or Single view and resize and arrange controls for more appealing structure. First have to remove the layout applied to controls. Forms built with wizard results in controls that are 'grouped' and cannot be independently sized and moved. Select all the controls simultaneously, right click > Layout > Remove.

2. The suggested code doesn't work because you are not using names of your fields in place of the generic alias in my example. Your table does have an ID field but it is an autonumber and code cannot set that value. The field you need to reference is Customer ID (or Emp ID if want to do same for employees). The number of a deleted record could be reserved. I do this by not actually deleting the record, just the data in the fields and then when I create new record, first search for one with empty field and if 'aborted' record found, open it as the 'new' record for data entry. This can get complicated. I still don't understand why this is needed. If you need a CustomerID that has meaning to users and to the customer, the autonumber can serve that purpose but it is not necessary for the numbers to remain in sequence, also would not want to reuse number that was already assigned to a customer. Why would you delete customer records anyway? Deleting records should be rare and only with strong justification.

3. Need to decide which ID field will be pk in tables. Instead of Customer Name in Calls table, should be the pk value from Customers table. Whether the fk fields for customer, resolved by, assigned to are number or text depends on which fields you assign as the pk in the related tables. You have the tables in the Relationship builder but until you decide the pk/fk fields cannot set links between tables. An issue with text fields holding number-like data is with filtering and sorting. Text is alpha sort even if the values look like numbers. So 12 sorts before 9 in ascending order. The unique ID I construct with code is like: YYYYA-####. The sequence starts over each year and 9999 is high enough limit to meet our needs. The structure will sort properly because the sequence number is always four digits: 2010A-0027, 2011A-0027, 2012A-0027, 2012A-4360.

4. Advise not to use spaces, special characters, punctuation (underscore is exception) in names nor reserved words as names. Better would be CustomerID or Customer_ID; EmailAddress, StateProvince, etc.