Creating A Custom Formatted Id Number

I'm trying to create an auto generating ID number field that contains a date followed by a sequential number for that date. In example...


tomorrows numbering would be
070720002 and so on.

Each new day would start a new sequence of 001, 002, 003, etc.
I have searched these forums and haven't really found any answers. Could someone direct me in the correct direction. Also I need this number to be saved in the table and not just displayed or printed.

View Replies


Creating A Custom ID Number? - Version: 2003 (11.0)

HI All,

This is my first post (this forum has been very helpful so far).

I am very inexperience with Access - I'm basically teaching myself as I create a simple database for work. It's a customer database (basically contact information) and I have everything working fine but I would like to be able to create a custom ID number using a portion of each customer's name and a number.

So for example: If I have a "John Smith" I would like an ID number along the lines of SMJ1234: SM for Smith and J for the first initial (the number could be random).

I would also like to make this ID number a primary key.

Is there anyway I can do this? I also have no idea how to write VBA.

Thanx for any help.


View Replies View Related

Convert Formatted Number To String - Version: 2007 (12.0)

I need to merge some data from Access with a Word document. I have a control called T_Sum, where the number is formatted as currency (Norwegian Kroner). It displays like "kr 10 000,00" in the control - due to the formatting, but in the table it is saved as "10000". When exporting to Word, it obviously "loses" it's format, and is displayed like in the table. I need the format from my control to be part of the export.

I think the way to do this, is by creating a String and copy the control value to the string. But I am not sure how to do this. This code does not work as it copies the unformatted number:
Dim strSum as String

strSum = Forms!MyForm!MyControl.Value

Help greatly appreciated.


View Replies View Related

Creating Custom PivotChart

I am trying to make a special kind of blob chart. It has to mimic the charts I have attached. I have a high number and a low number for everything that someone might want to put on the axes. The eventual goal is to have a form were the user selects what goes on each axis, and have a few other possible choices to limit how much is displayed. What I am making doesn't have to be as fancy, flexablity is more important. Can Access make this kind of graph or am I on my own, making shapes in VBA?

View Replies View Related

Creating Custom Letters????


I would like to create a custom letter where I can type the body of the letter and it will print the letter for every customer in the database.


View Replies View Related

Pages - Creating A Custom Theme


Can anybody help?

I would like to create a custom theme for my INPUT page, however within Access 2003 I only have the option of about 20 predefined themes. I want to insert my companies' logo onto the INPUT PAGE.

What I would like is to save a template, like I have done in REPORTS, so that when I build a INPUT PAGE I can select my own theme to use, based upon my companies' logo, type face et al .,

Thanks and kind regards.

Phil Drury.

View Replies View Related

Custom Generated Number

Quote by Pat Hartman
>>>BTW, one somewhat undesirable thing happens when you go with replication. Access changes ALL your autonumbers to random instead of sequential. That means that from that time on, all new autonumbers will be assigned randomly to minimize the problem of conflicts between the design master and replica sets. A random autonumber can range from a very small negative number to a very large positive number. These autonumbers can no longer be used as your customer numbers or order numbers if these numbers are ever printed or given to the customer to be used for reference. A customer probably will be confused by an order number = -2. If this is going to cause you a problem, you'll need to switch to custom generated numbers. Post back for details if you need them.<<<<

I'd be interested in getting some details. Data will be entering that table through a data entry form and through appending from a second copy of the DB. I want to make the PK a random autonumber but then when the second DB is added to the Master DB have the table in the Master DB assign each record a "prettier" more usable id number. I know this is stupid but I'm not sure how to relate code to a table (there's no before update event for a table as far as I'm aware).

Thanks for any help you can give.


View Replies View Related

Creating Custom Date Format After Import

I am stumped: After importing from an outside source (an excel spreadsheet) I have to change the short-date format
that I have just imported into a very specific custom format: "yyyymmdd" Ex: 20040828. How do I convert into this
new format? Help will be greatly appreciated...

View Replies View Related

Creating Custom Menu - Version: 2002 (10.0) XP

I have been asked on a new database to create a menu that only shows specifc things. First I really wanted to create a only words like the default File and then place commands within that new File. But when I create a custom it doesn't allow me to add to the custom menu when I drag the commands to the menu. Then I tried to create just a toolbar. It allows me to add commands but then it won't let me place them in a format similar to the default file. Please advise what I can do.

View Replies View Related

Creating A Custom Menu Bar - Version: 2003 (11.0)

I am trying to create a menu bar with custom command buttons on it and I want to be able to see it no matter what form I am in and I don't want it to hover over an opened form. Is this possible?

View Replies View Related

Creating A Custom Function - Version: 2000 (9.0)

I've been using Access 2000 for 4 years and am learning it as I go. I have built a database for my boss and sometimes the requirements are a little odd. So far I've been able to accommodate them. Following is one that I've not been able to figure out.

Using message boxes, I know that the VBA code does what I want it to do; however, I am unable to insert the two calculated fields into the Report or its underlying Query.

Can I create a Custom Function that will calculate txtTopLine and txtBottomLine??

I hope that someone can help or at least direct me to someone who can.

The out come is a set of hanging file folder labels that look like this:

: [txtProjectNo] [txtTopLine]:
: [txtBottomLine] :

My boss wants txtProjectNo to be a larger text and bold.

txtTopLine (Building Name and Room) and txtBottomLine (a brief project description), are to be of a smaller text and not bold. It is to be Right Alligned and contain only Building Name and Room.

The problem comes if txtTopLine is over 33 characters. It get's cut off. To solve this, my boss wants the cut off part put in front of txtBottomLine.

I came up with a VBA code that does this, but I can't get txtTopLine or txtBottomLine to show up on the tabs.

Here is the code:


Dim Lne1 As String
Dim txtBottomLine As String
Dim txtTopLine As String
Dim Lne2 As String
Dim txtRemaining As String
Dim N As Integer

Lne1 = Trim([txtBuildingName] & " - " & [txtRoomNo])
Lne2 = [txtBriefDesc]

'If Lne1 is over 33 characters, it will be cut off
'Test to make sure Lne1 is not over 33 characters
'If under 33, then all is fine
If Len(Lne1) < 33 Then
txtTopLine = Lne1
txtBottomLine = Lne2
GoTo SkipDO

'Calculate txtTopLine and find the remainder

'Test to make sure Lne1 is not split in the middle of word or number
'First test to see if the 33rd or 34th characters happen to be a space
N = 33
If Mid(Lne1, N, 1) = " " Or Mid(Lne1, N - 1, 1) = " " Then
txtTopLine = Lne1
End If

'Start backing up until split is at a blank space
Do While N > 0
N = N - 1
Exit Do
Loop Until Mid(Lne1, N, 1) = " "

txtTopLine = Left(Lne1, N)
'txtTopLine is now defined

'Calcuate txtBottomLine
txtRemaining = Right(Lne1, Len(Lne1) - Len(txtTopLine))
txtBottomLine = Trim(txtRemaining & ": " & Lne2)

End If

View Replies View Related

Creating Custom Charts - Version: 2000 (9.0)

Is there any way to create a chart that uses values one a form or report rather then getting the values from a queary or table?
I'm trying to add charts to my reports.

View Replies View Related

Creating A Custom Menu - Version: 2000 (9.0)

What I would like to do is create a menu that drops down just like the "Tools" and "Window" and other menus that drop down from the top bar. I would like to call this Menu "Maintainance" and offer options to change information on the back end that is not otherwise available, such as changing passwords.

For example, the user would select "Change Model Year" and a popup box would ask them to input their password. If input correctly, the user gets confirmation that now the Model year has been incrmented. It would have another button on that box to offer to open the Model Years list to be editied.

I know there is a toolbar editor, but I would prefer my menu as a drop down list. Can I do that? And programming what would happen in VB is not hard, but it said on the toolbar editor I can only use Macros. I have no clue how to do that.

Any help with this would be appreciated.

View Replies View Related

After Login, Suggestions On Creating A Custom Log-in Page


I have impletmented security in my database. I have users login which gives them restricted access to certain areas of the database. I would like to have a page come up, after they login, that lets them see any tasks they have, any messages, etc... How would I go about doing this?


View Replies View Related

Creating A Custom Toolbar - Version: 2003 (11.0)

When I try to create a custom toolbar, it says that I must have a db open to create a custom toolbar. So I open one of my db (which is NOT read-only) and try again to create a custom toolbar and now the "Customize" is dimmed out. Sounds like a catch-22 to me.

With a db open, I am able to customize the "Utility 1" toolbar. Then I go to Startup to set Utility 1 as the Menu Bar and it is not there, probably because Utility 1 is not a Menu Bar. So again how do I create a custom toolbar that I can use with my forms and reports? I tried to put Utility 1 in the toolbar property for the form, but when I exited the form and then brought the form back, I got an error.

One of my apps is getting close to operational for the user and before making a runtime file, etc for them, I want to have things setup correctly in the Startup which includes a custom menu bar.


View Replies View Related

Creating A Custom Control - Version: 2007 (12.0)

What I want to do is create a control (I believe that is the right teminology, I mean something like a textbox/combobox/listbox) which I can make up from different premade controls.
The reason I want to make a control is as alot of applications that I make have similarities for example address boxes (which I make up of 6 text boxes, a groupbox and then VBA to control the formatting of the address) and if I could make it into a control which I can just drag and drop onto my forms it would save alot of time.
I have googled this and I haven't found any sites which seem to be useful (so this probably is that I am not using the correct name or something).
So I was wondering whether anyone knows of any websites which give details on how I would be able to do this.

Thanks for any help in advance

View Replies View Related

Creating Custom PO Numbers - Version: 2003 (11.0)

I am creating custom PO numbers when PO's are created using the following code...
Me.PONumber = Nz(DMax("PONumber", "tblOrder_PO"), 0) + 1

If some of the products come in the user will check them off as received, but some products may be on back order. So from the PO order form I will need to create a new PO with the products not received so that this PO can be completed. But what I want to do is keep the PO's with the first beginning number.

For example, if I have PO# 1 and I have to create a PO off of this one I want to make the PO# 1-1 and then if I have to make a PO off of 1-1 then make the number 1-2.

Can this be done?

View Replies View Related

Custom Line Number On Report ?

I try to explain my problem.
There is a report have 50 records.
I print the report first time and Set the number by Data>running sum method. This start from 1 to n...

I require a Form Where i enter to initial number example 22 and say preview report.
The report Set the line number of Details Section Like
24 etc...

How can i do this. Please help

View Replies View Related

Creating A Custom Shortcut Menu - Version: 2007 (12.0)

Hello everyone. I am sure this a basic issue but I have been chasing my tail trying to figure this one out. I have read technical documments pertaining to the use of a custom shortcut menu using macros and also by using code. The information calls for using a AddMenu macro or code but both methods require a MenuMacroName reference. How do a go about making a macro that contains a menu item (such as Print) that I can use? If I am going about this all wrong, any help getting me back on track would be appreciated. Basically, all I am trying to do is enable the print command (and only that command) when a form opens. I do not want the user to see "form view", "layout view", etc when they right-click.

Thanks for any help,

View Replies View Related

Creating A User Defined Custom Report - Version: 2003 (11.0)

I have been searching for a method of making user defined reports from a form. The only reference I have found if this: Custom Reports .

I have tried using this example in my database but I keep getting a "Application-defined or Object-defined" error message. Unfortunately for security reasons I can not upload the .MDB to show everyone what Im talking about. So I was wondering if anyone has an example .MDB with something similar to that link that I can look at.

Much appreciated,

View Replies View Related

Dynamically Creating Number Of Bookmarks Matching Number Of Acce - Version: Any Version

Dear Access Gurus,

I dont intend to take up much of your time.

Here is the problem, I have a variable eg. 'clothes' in my database which is not fixed in number. I need to merge this into a word document. The problem is that the 'clothes' column can be anywhere from say 5 to 500! Highly varied. And I cant have 500 different word templates. I need a way of dynamically creating a matching document with the same (or approximate) number of 'clothes' bookmarks in Word as the number I have in Access (which can vary). So, for example, If I have 4 clothes in one case, the word document will be produced with 4 clothes bookmarks. If I have 18 in another, the bookmark will be populated 18 times (in successive paragraphs).

Here is an example of what I want to achieve with say 3 'clothes'

This is shirt (i.e. clothe1)

This is skirt (i.e. clothe2)

This is jeans (i.e. clothe3)

To be honest, being relatively inexperienced compared to other users on this forum, I really do not know how to approach the problem. I know how to merge when I know the exact number but not when it can vary. How would you guys approach this PLEASE? I suspect I need a Loop somewhere but im not sure how to structure the coding.

Thank you very much :-)

I am desperate for help!

If my posting is not clear enough, I will be happy to clarify.


View Replies View Related

Creating A Random Number

Quick question, how do I have access generate a random number between 1 and 8? I want it to generate either a 1,2,3,4,5,6,7, or 8. (no zero, no nine)

Expr1: Fix(Rnd(8-1)*10*Rnd()) is what I have been using, but this always generates the number 2? I do want the decimals to be rounded to the nearest integer, thus I have Fix.

If someone could please help me, it would be greatly appreciated! Thanks in advance.

View Replies View Related

Creating Reference Number From 3 Fields

Hi there,

I'm making a document control database where the a unique reference number is automatically generated from 2 tables and updates a 3rd table.

xxxx or yyyy/lookup in 2 table/autonumber

I have been trying to do this is a form but because the control sauce is a calculation 'Text1&Text2&Text3' I am unable to update the 3rd table with the combined reference number.

I have limited knowledge particularly of VBA coding

Any help much appreciated

View Replies View Related

Need Help Creating Auto Customer Number


I am v new to Access and am creating my first DB.

I want to create a unique client office code....

For example, for a (law firm) client name Brown Smegmore in Sydney,
I want to create a code like BS_S_1, Brown Smegmore in Melbourne: BS_M_2

I would really really like some simple help



View Replies View Related

Creating Automatic Number - Not AutoNumber - Version: 2007 (12.0)

I have an inventory control database. The product information is entered into the db through a poduct details form. I would like to automatically populate the stock number control on this form when a new item is added. I have existing numbers from 1 to 15200. Not all number in between are used, there are some large gaps in the numbers.

What I would like to do, if possible, is have a number generator that serches for the lowest unused number and then assigns that number to the stock number for the new product.

Is there a way that this can be done?


View Replies View Related

Creating Specified Number Of Rows In A Subform - Version: 2007 (12.0)

Hi all,

I've got a database that lists information about some animal breedings. There are two tables that contain data:

tblAnimal: fields here are pertinent to each animal -> id, gender, animal_fam, DOB, and other fields defining charachteristics of the animals (color, etc) . DOB is the date of birth.

tblBreeding: fields here are pertinent to the breeding -> dad_id, dad_fam, mom_id, mom_fam, date_mated, date_separated, date_birth, pups_fam, num_pups, litter_id_start .

I've got a form (frmBreeding) that can be used to enter the breeding information. The num_pups field lists the number of offsprings born in that litter. There is a subform (sbfrmAnim) on this form that is linked to tblAnimal

My question is that I would like to create new rows of data in tblAnimal based on the number of pups (num_pups) value and then automatically fill in some of the information in tblAnimal. For example, a breeding results in 7 pups and these pups will be gived ids 80 through 86. This means that num_pup=7 and litter_id_start=80. Therefore, I want 7 rows to be created in sbfrmAnim and the ID fields in the subform to be populated by the numbers 80 through 86. I also want the date_birth value from frmBreeding (linked to tblBreeding) to be filled into the DOB field in the sbfrmAnim (thus updating tblAnimal).

The additional data for gender and other charachteristics can then be entered for each pup in each row.

I realize that the date issue results in replication of data which should not be done, but the trouble is that we don't just get animals from breedings we perform. Sometimes animals are given to us from other breeders and for this reason we have to put the animal's birth date into the animal table.

So, I was hoping that someone can help me with this. I don't know if I'm complicating things more than they need to so if there is a better and easier way to go about this, I'm all ears. Thanks a heap in advance.

- O

View Replies View Related

Creating A Custom "What's This?" Button In Forms

Is there a way to use a command button to perform the exact same function as the "What's This" (?) button on the title bar? Is there code I can give it "On Click" that will make it do the same as as the What's This button?

View Replies View Related

Error With Creating A Sequential Number That You Can Control - Version: 2003 (11.0)

on this website i got a way to create a sequential number that i could control. I adapted it to my database and worked fine. But, when i did it to another database it wont work and comes up with errors that even on the site explains that might happen. It gives you thr remedy to the errors but still doesnt work. Can anyone work it out or are there other examples out there to do similar tasks?

Thanks in advance,

View Replies View Related

Create Custom Constants List For Custom Function - Version: 2003 (11.0)


I've created a function which also has a optional input string, like this:

Public Function fBijeenkomst(sNetwerknummer As String, Optional sFieldname As String) As String

Now there are several input option for the second string. To make things easier, I'd like to add a constants list, just like some of the build in functions do have.

Is this possible?

View Replies View Related

How To Use Custom Images In Custom Ribbon Menu - Version: 2007 (12.0)

I have been experimenting with creating a custom ribbon for my Access 2007 application. All works great if I use the images from the Office Icon Gallery. I was reviewing the Ribbon Extensibility document written by Clint Covington and Viki Seleca from Microsoft and it does provide some code snippets for using your own image, but I get errrors when loading the application. The error I get is: "The attribute 'getimage' on the element {}button' is not defined in the DTD/schema.

I am new to this whole xml thing so I"m not sure where to go from here...can anyone help?

Below is an example of the code I have in my USysRibbons table:

<customUI xmlns="">
<ribbon startFromScratch="false">
<tab id="tabMain" label="MyStuff">
<group id="grpMain" label="Main">
<button id="cmdMainMenu" label="Main Menu" onAction="Ribbon.OpenMainMenu" size="large" getimage="OnGetImage" supertip="Display the Main Menu."/>

View Replies View Related

Custom Menu Call Of A Custom Function/Sub - Version: 2003 (11.0)

OK... I have a subroutine that looks like the following (really simple so far as I am just trying to get this to work)

Public Sub mysub()

MsgBox "me"

End Sub


I configured the 'Custom' object in toolbar with

OnAction = mysub()

Access proceeds to tell me that it can't find the function I specified...

Any thoughts? I've had this working before but it doesn't seem to anymore...


View Replies View Related

How To Look Up Formatted AutoNumbers?

Good evening,

I am a little confused. I have one table with the Autonumber field and another table with a field that looks it up.

Tha problem is that I am using a Format for my Autonumber, e.g.
"O0045". And the field with the foreign key refuses to accept it! It needs a number to be entered, i.e. 45, and then it can format it. So on the whole, the LookUp Feature does not work, because it suggests FormattedAutonumbers, which do not fit in (obviously the foreign key accepts numbers only).

Is there a solution? Thank you in advance.

View Replies View Related

Special Formatted RTF Export

Ok so I have a bunch of data collected from interviews. I need to export this data into a rtf format.
I'm going to be using TAMS for analysis. So I'd like to specifically format the output

for instance say I have multiple records that contain this info

interview number = <Number>
barriers faced= <Text String>
postal code = <text string>

I'd like them to be formatted like so for each record


{!context ID Post_code}

{ID} *number from interview number here* {/ID}
{POST CODE} *Text from post code here*

{Barriers} *text string here* {barriers}



then the next record

Thanks for any help provided!

View Replies View Related

Copyrights 2005-15, All rights reserved