Help required please

[namliam]

All needs to be writen in VBA.

I would probably go with a sequence number that is unique, starting at 10001. Or an autonumber that simply starts at 1.

Now create a function that takes the Lastname as Input (possibly the AutoID too). Then it gets the unique number and takes the last 4 numbers to add to the last name (possibly padding the number with 000, if needed).

For the first 10000 customers you are guaranteed a unique ID of XXX9999
Now you need to search your usernames to make sure that the new userid doesnt exist yet...

For this lookup "openrecordset" in the access help or on the forum... to give you an idea of what to do.

Now the problem with this userid is... if your customer base gets big enough ....
You will run into the situation where you have 10000 unique customers named
Jansen
Janssen
Janszen
Jansens
Janszens
Janssens

Yes this is true in the NL and Jansen is actually one of the most common surnames together with Vries.

Now XXX9999 no longer fits to make a unique userID... So what then???

 

[roadrage]

All needs to be writen in VBA.

I would probably go with a sequence number that is unique, starting at 10001. Or an autonumber that simply starts at 1.

Now create a function that takes the Lastname as Input (possibly the AutoID too). Then it gets the unique number and takes the last 4 numbers to add to the last name (possibly padding the number with 000, if needed).

For the first 10000 customers you are guaranteed a unique ID of XXX9999
Now you need to search your usernames to make sure that the new userid doesnt exist yet...

For this lookup "openrecordset" in the access help or on the forum... to give you an idea of what to do.

Now the problem with this userid is... if your customer base gets big enough ....
You will run into the situation where you have 10000 unique customers named
Jansen
Janssen
Janszen
Jansens
Janszens
Janssens

Yes this is true in the NL and Jansen is actually one of the most common surnames together with Vries.

Now XXX9999 no longer fits to make a unique userID... So what then???

Thanks....but i'm not having much luck with this openrecordset. And with regards to reaching over 10000 customers i'm not concerned as long as it works up to 10000.

 

[namliam]

A simple sample for open recordset.

Dim rs as DAO.recordset
set rs = currentdb.openrecordset("Select... from ... where ... etc ...")
if rs.eof then
    msgbox "No records found" 
else
    mgxbox "Yes this is found!"
end if
rs.close
set rs = nothing

i'm not concerned as long as it works up to 10000.

This would be demerits for my students personaly.

 

[roadrage]

A simple sample for open recordset.

Dim rs as DAO.recordset
set rs = currentdb.openrecordset("Select... from ... where ... etc ...")
if rs.eof then
    msgbox "No records found" 
else
    mgxbox "Yes this is found!"
end if
rs.close
set rs = nothing

This would be demerits for my students personaly.

thanks again, been slow to respond as ive lost my internet at home. The deadline for this unit of work is may 16th so i need to get it functioing asap. that is why i am willing to overlook the limitations for now. Sorry to sound stupid but i can't seem to get my head round how the above code will allow me to achieve what i want? Can you explain further please?

 

[gemma-the-husky]

roadrage

by the way you said at the top that this is to be a web application?

again, you are making this extraordinarily complex and difficult for secondary scholl students. I would say forget the web aspect - just try and produce a working database.

 

[namliam]

You would need to use the funtion to check for uniqueness.

An easy -quick- way to get 95% proof unique keys and atleast for the first 10k
Use an autonumber to generate a(real database) primary key.

Now concatinate the lastname and autonumber (left padded with zero's) to generate your functional key. Guaranteed to be good for the first 9999, good odds on beeing good even for the next 9999, odds go down as you get to more...

 

[roadrage]

roadrage

by the way you said at the top that this is to be a web application?

again, you are making this extraordinarily complex and difficult for secondary scholl students. I would say forget the web aspect - just try and produce a working database.

no i think you may have misread, we don't need a web based front end just a database. I am trying to produce a working database but i am trying to understand how i can get the first three digits of their name followed by four numbers.

 

[namliam]

Left and right functions are your friends

Lookup the access help teach!

 

[roadrage]

Left and right functions are your friends

Lookup the access help teach!

Ok so now i have the following Left([Customer_Name],3) & Right([Customer_no],4)& "000" where do i put this. I can place it with the above code? And how can i get this to be executed and update another field in another table that will contain the default value of "free" ???

 

[namliam]

You dont...

This is a calculated value that doesnt mean anything in your database. You just display it where ever you need it...
Firstname: The
Lastname: Mailman
Customer_No: 14

Your function would make: Mai14000

Firstname: The
Lastname: Mailman
Customer_No: 1414

Your function would make: Mai1414000

Is that what you want??

Regards & Good luck

 

[roadrage]

Expr1: [Forms]![Frm Create Booking]!Left([Forename],3) & ([Cust no])



Can anyone tell me why i am getting the following error message when i try to run the above query?

"Undefined function '[Forms]![Frm Create Booking]!Left' in expression.

What i am trying to do is get a customers first three letters of their name adding the customer number it from a form. Any ideas?

 

[Rabbie]

You can't mix functions and field names as you have done

TRy

Expr1: Left([Forms]![Frm Create Booking]![Forename],3) & ([Cust no])

And see how it goes

 

[roadrage]

You can't mix functions and field names as you have done

TRy

Expr1: Left([Forms]![Frm Create Booking]![Forename],3) & ([Cust no])

And see how it goes

Hey thanks, that works great!!!

 

[roadrage]

You can't mix functions and field names as you have done

TRy

Expr1: Left([Forms]![Frm Create Booking]![Forename],3) & ([Cust no])

And see how it goes

One more question if i may? At the moment i am having to type in the customer number. Is there any way that this could be retrieved so that each time a new customer is entered a unique number could be assigned without me having to remember which one i have already used???

I thought about using a separate table with autonumber for "cust_no" and pull from there but i can't get my head round how i could get the query to pull unique number for every new customer?? Hope i am making sense?

 

[namliam]

how about you just add an autonumber to your existing table and add that as your primary key

 

[roadrage]

how about you just add an autonumber to your existing table and add that as your primary key

I don't think that would work. At the moment there are no customers. The append query from above populates the table using a customer number that i enter and the first name which gives customer id.

How would i get the query to recognise that the customer id must contain the autonumber? It would just keep overwriting itself no?

 

[roadrage]

can anyone explain the function below to me? I understand the first part its after the "&" i can't get my head round?

left( [textstring],3) & int(1000+ rnd()* 8999)

also how do i find the max of an autonumber and then increment it by 1 every time in a query?

 

[wiklendt]

if i were you, i'd hope my students never see this thread! what a perfect way to do the project to the same completeness/functionality as my teacher!

 

[speakers_86]

OMG. Your teaching this in a class not specifically about access???

It sounds like you should be focusing on the principles of a relational database, rather than intermediate level queries.

As for your question

can anyone explain the function below to me? I understand the first part its after the "&" i can't get my head round?

left( [textstring],3) & int(1000+ rnd()* 8999)

int() returns the integer portion of a number
rnd() returns a random integer

So this is returning the integer part of 1000+(random#*8999)

also how do i find the max of an autonumber and then increment it by 1 every time in a query?

Create a query. Add the table in question. Add the autonumber. Group by Max. In the next column, create a calculated row which is ["Name of autonumber Field Here"]+1

 

[roadrage]

if i were you, i'd hope my students never see this thread! what a perfect way to do the project to the same completeness/functionality as my teacher!

Thanks for your help....not!!