Access Questions.

[skip_skip]

I would like to know how to allow users to edit records with missing information and allow users to edit certain fields that would need to be updated from time to time so that they cannot accidentally delete information or type over information in an existing record.

How could I also tell Access to open in a new record to prevent typing over an existing record?

I would also like to know how to total all of one customer's accounts (ex: if he had 3 open accounts, I would like the sum of all his accounts by his name instead of showing it per loan number and appearing 3 times in a query).

And, how would I go about separating a field that contained the customer's first, middle, and last names into three separate fields for each?

Thank you soooooooooooo much for any assistance in these areas. I've been trying to figure it out but have not quite gotten the results I am looking for.

 

[FoFa]

1> You can control that in a form by setting each control (field) to locked = true (or false) as one way.
2> On a form you use the DoCmd.GoToRecord , , acNewRec command usually
3> SELECT CustomerName, sum(AccountTotal) from Customers where customerID = 12345 GROUP BY customerID
4> You basically have to parse the data, search the forums for examples

 

[skip_skip]

Access Questions

Thanks for the suggestions.

I've tried it and I'm not sure if I am doing it right so I'm going to show my stupidity and see if this is the right way or not:

1: Lock = Yes or No only, if I tell it Yes, then it doesn't allow for any edits, or entering of data for new records.

2: I'm not quite sure how to do that, is it in the properties or expression builder? (DoCmd)

3: So if I have controls (fields) labels: Cust_ID; Short_Name; Curr_Bal_Ln; Orig_Bal; and Remaining_Commitment and I wanted a sum of those balances, I need to build an expression that is sort of like:

Short_Name, sum(Curr_Bal_Ln); sum(Orig_Bal); sum(Remaining_Commitment)

4: I took the Short_Name field/control and parsed the first name using: Left([Short_Name],InStr(1,[Short_Name]," ")-1) and when I did that it turned over 7,000 records into 10 records so I don't know if what I did was what I was wanting to do. I want it to turn from [John P Smith] into [John] [P] [Smith] and I think when I did that it only returned 10 records' first name and lost the rest.

Hope I am making sense. I am somewhat of a new user to Access and have learned by playing with Access.

Thank you for the help!!!!!!!!!

 

[WayneRyan]

skip,

1) Lock the fields that you DON'T want changed.

2) You can make a command button (maybe use the wizard) and in its OnClick
event put [Event Function] and the acNewRec that FoFa said.

3) Experiment with the queries, pressing the Sigma sign on the Toolbar will
let you do summation queries.

4) You can use make new fields, use an update query with the Mid function
to extract the name parts.

If we break this down into several small tasks we will address them.

Wayne

 

[skip_skip]

Access Questions

thanx, wayne. will keep workin on it. Merry Christmas all!!!

 

[skip_skip]

Parsing Data

Thanks to FoFa & Wayne for your suggestions. I have attempted to do your suggestions, don't think I'm doing it right since the results are not what I was looking for.

I've also searched the forum for parsing data in access and haven't found anything that would help. I am using XP w/Access 2000.

We have a main database out of our office in Austin that is sent every night to me in a zip file that contains over seven thousand records.

I need to parse out the name field, which may also contain a company out.

EX: SMITH, JOE B JR OR SMITH JOE B JR OR K & L COMPANY INC
needs to be
joe
b
smith
jr

 

[skip_skip]

Parsing Data

Sorry, guess I hit enter without knowing where my curser was. I need that data parsed without losing the company name information. What I have tried has not worked, I've gotten error messages such as the expression On Click you entered as the event property setting produced the following error: Ambiguous name detected: New_Record_Click; The expression may not result in the name of a user-defined function or [event procedure]. There may have been an error with the function, event, or macro.

Please help.

Thanks a bunch!!!!!!!!!

 

[FoFa]

You could create a couple functions and handle the data youself in a vba script. Here are a couple off the top of my head (may need tweaking):
function fNumArgs(StrIN as string) as long
dim Cntr as long, Col as int, TestStr as string
TestStr = trim(StrIN)
if len(TestStr) < 1 then
fNumArgs = 0
exit function
end if
Cntr = 0
Col = 1
do while Col <> 0
Cntr = Cntr + 1
if Col + 1 > len(TestStr) then exit do
Col = instr(Col + 1,TestStr," ")
loop
fNumArgs = Cntr
exit function

function fPullArg(StrIN as string, NumArg as long) as string
dim Cntr as long, C1 as int, C2 as int, TestStr as string
TestStr = trim(StrIN)
if len(TestStr) < 1 then
fPullArg = ""
exit function
end if
Cntr = 0
C1 = 0
C2 = 1
do while (Cntr < NumArg AND C2 <> 0)
C1 = C2 + 1
Cntr = Cntr + 1
if C1 > len(TestStr) then exit do
C2 = instr(C1,TestStr," ")
loop
fPullArg = mid(TestStr,C1,C2-C1)
exit function

Call the fNumArgs to get the actual number of parsed fields, then call fPullArg to pull the actual argument specified out. Error checking needs to be added, etc.

 

[Ccook]

new record

I hope this is not too late to help, but a simple way to make a form open to a new record is to change the form property- data entry- to yes.


Cheryl