Bounded And Translated Custom Autonumber

Nov 2, 2005

I was hoping to run this by you guys to see if I'm doing anything horribly wrong. I have done a number of searches related to custom Autonumbers and I think this is okay but any suggestions/confirmations would be greatly appreciated.

I want to create a random Primary Key that will also be used as an item #. I know that in many cases this is frowned upon *but* I do not need the number to hold any significance I just want it to be unique. However I want to represent the item # as a set of 6 Hex digits so the standard Long Integer is too large. 16^6 = 16777216. No 0 index so 16777215.

I create my own bounded number with a macro (=Int(Rand(16777216-1))+1 )and put that as the default value. It is indexed and set to no duplicates. Is this correct?


When I display the field in a form or report I call a module which translates the number into Hex and appends leading zeros. I am currently having a problem where inserting a new record does not display the translated index correctly, but after it has been inserted it is fine.

If you have any comments or suggestions about how this could work even better (or if this wont work at all!) please let me know.

View Replies


ADVERTISEMENT

Custom Autonumber

Mar 29, 2005

I am in the process of developing a DB for work. The DB is used to track incident reports and lost/found property reports.

I was interested in using 2 custom autonumbers for 2 different reports within the same DB. I would like to use the format of "I" for an incident report, "F" for the found property reports. So when I start a new Incident report it would generate the following number; "I05-03-001" The "I" specifing that it is an incident report, "05" for the current year, "03" for the current month, and "001 as the next sequential number for the reports for that month.

Does anyone have any suggestions on how I can get this done? I would very much appreciate your help.

John

View 1 Replies View Related

How Do I Create A Custom Autonumber

Mar 2, 2005

I currently have a few tables that use an autonumber as the primary key, however, I would like the autonumber to start with a series of letters if possible. For example: instead of it creating an ID of 1, then, 2, 3, 4, and so on, I would like it to append lets say "ABC" to the front of it; ABC1, ABC2, ABC3, etc.

AP

View 2 Replies View Related

Primary Key...Autonumber Or Custom?

Jul 10, 2005

Just a general question...
When you make a primary key is it better to use and Autonumber or a Custom one? I have for the most part used auto...easy, convienient and well I have never had a problem yet (been pretty serious on Access for about 1 year). But I just read that ref integ doesn't work on Autonumbers? which would also mean cascading update & deleting correct?
I have looked at Northwind Access sample and all except "Customer" they use Auto. Well then to make a custom Primary field wouldn't I have to do that at the form level? like "CustomPrimKey = Left([CustFirstName],2 & Left([CustLastName],2) & [PriKeySetAtAutoNum]" or something like that to get what I want. I imagine you would automate this as opposed to letting the operator manually enter them.
And then as in the Northwind sample how do you know which tables need the custom numbering and which ones don't.
As I have learned on my own some of the "basics" elude me until they come back to bite me.

Thanks

View 10 Replies View Related

Custom Autonumber Based On Lookup

Jul 8, 2013

Custom Autonumber based on lookup. I am creating an access database where autonumbers to be work.

I have a table with Segment Name my main table will lookup the values of segment table.

Based on the Segment I choose Autonumber has to be created

My main table to house each record (tbl_import) has the following fields:

ID
Segment
Port
MOT
TOTAL CIF

Child table - Segment contains
Development
Deployment

Testing

So based on segment field which I choose

Development autonumber has to set DEV-1001 and Deployment has to set DEP-2001, TEST-3001

My thought is if there a way to code the Segment field to lookup the segment what I choose based on the Autonumber series starts (similar to a vlookup in excel), then concatenate the DEV in the Record ID field.

View 2 Replies View Related

Forms :: Custom Autonumber With Year And Month

Aug 12, 2013

I need to create an "autonumber" field in the following format:

FYYMMXX

Where F is a constant, YY is the year, MM is the month, and XX is an incremented number. So for example, the first record in August of 2013 would be "F130801".

I've been checking other threads but haven't been able to follow them to a resolution. I think there is a way to do this within my form...

View 3 Replies View Related

Linked Excel File - Excel Date Field Translated To Text?

Mar 23, 2006

I have an excel file linked to a table in Access. Several fields are date data types in excel but are showing up as text fields in Access.

My real goal is to do a comparison between two tables, but only if the date of the one piece of data is newer than the other. I had planned on comparing the two date fields but even though I have formatted the date fields in my excel file to be "Date", when I look at the design view of my table it is showing up as "Text" and therefore I am unable to do this comparison.

I'm not sure if it's just something that I'm missing but maybe someone else knows an easy fix to this. I know this is probably a simple question, but I did search the forum and didn't find a thread that specifically dealt with this issue.

Thanks in advance for your help.

View 2 Replies View Related

How To Add Constraint To Access Bounded Form.

Jun 26, 2005

Hi everybody .I created a bounded form that allows user to enter the following fields information
to the sql server db that is linked to access .The fields are: Projectno, taskcode, taskdiscription
and total hours worked.

I want for example when a user tries to enter total hours work for a user (Hours in hours worked table) and
if he did not already enter wages of the employee for that particular project (amount in hourly wages table)
that user get a massage prompt and ask user to enter amount in to hourly wages table first. Possibly opening that form.

I be happy if some one help me add this feature to my form. Thanks


http://i5.photobucket.com/albums/y180/method007/constraint.jpg (table reletionships pic)

View 1 Replies View Related

How To Delete Current Record In Bounded Form?

Jun 19, 2005

Hi guys. I made bounded maintenance form customer table in my access db.
But when I try to delete a record by clicking on the delete button I get
the following error. I be happy if some one help me delete record successfully. Thanks


Code:Run-time error '91'Object variable or with block variable not set
http://i5.photobucket.com/albums/y180/method007/deleteerror.jpg
pic ===>delete error


Code:Option Compare DatabasePrivate Sub cmdSearch_Click() Dim strStudentRef As String Dim strSearch As String 'Check txtSearch for Null value or Nill Entry first. If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!" Me![txtSearch].SetFocus Exit SubEnd If'--------------------------------------------------------------- 'Performs the search using value entered into txtSearch'and evaluates this against values in customerno DoCmd.ShowAllRecords DoCmd.GoToControl ("customerno") DoCmd.FindRecord Me!txtSearch customerno.SetFocus strStudentRef = customerno.Text txtSearch.SetFocus strSearch = txtSearch.Text 'If matching record found sets focus in customerno and shows msgbox'and clears search control If strStudentRef = strSearch Then MsgBox "Match Found For: " & strSearch, , "Congratulations!" customerno.SetFocus txtSearch = "" 'If value not found sets focus back to txtSearch and shows msgbox Else MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", _ , "Invalid Search Criterion!" txtSearch.SetFocus End IfEnd SubPrivate Sub Command14_Click()'''On Error GoTo Err_CmdAdd_Click '''Me.DataEntry = True '''Me.CmdFilter.Visible = False '''Me.CmdShowAll.Visible = True '''DoCmd.GoToRecord , , acNewRec If DCount("*", "Customer") = 0 Then Me.customerno = 1 Else Me.customerno = DMax("Customerno", "Customer") + 1 Me.customerName.Value = " " End If '''Exit_CmdAdd_Click: '''Exit Sub'''Err_CmdAdd_Click: '''MsgBox Err.Description '''Resume Exit_CmdAdd_ClickEnd SubPrivate Sub cmdDelete_Click()Dim x As Variantx = MsgBox(" You are abut to delete " & Me.customerName & " from this table - proceed ? ", vbOKCancel)If x = 1 ThenWith myRS.Delete.MoveFirstEnd WithEnd IfEnd Sub

View 1 Replies View Related

Error Updating Record In Bounded Form

Jun 19, 2005

Hi guys I am creating maintenance form to update and add new records to department table.
But when I try to edit department field in employee table.
(I get the following error.I get same when try to add new record.)

Error:
You cannot add or change a record becasue a related record is required in table department

http://i5.photobucket.com/albums/y1...gdepartment.jpg
==> pic of error errroupdatingdepartment

http://i5.photobucket.com/albums/y1...eletionship.jpg
===> timekeepingreletionship


I be happy if some expert help me how to update and add new record to department table successfully without the above error and without changing the db structure.
Thanks

View 2 Replies View Related

Problem With Adding Deleting Records In Bounded

Jun 23, 2005

Hi guys i a have bounded form that supposed to update/add and delete records from customers table. But it is acting very weried when i try to use it. For example in the pic below it shows my form trying to
add a new record number 8 .

http://i5.photobucket.com/albums/y180/method007/tryingtoaddrecord.jpg

After i added new record and if i press the button to bring me to first record i see my new record added to begining of list and if i press button to take me to last record which is number 8 it shows its customername value as blank! I be happy if some one help me fixt this strange problem. Also the first record in the db get delete with new record!

http://i5.photobucket.com/albums/y180/method007/beforeaddingrecords1.jpg ( value in table customer before adding new record)


http://i5.photobucket.com/albums/y180/method007/lastrecordshowsblank2.jpg


http://i5.photobucket.com/albums/y180/method007/afteraddingnewrecord3.jpg ( vlaus in table customer after adding new record .first record get deleted with out my intention!)

Now if i try to delete a record. should i number all the records or not ? I be happy if some one show me how to solve this problem as well.Thanks

http://i5.photobucket.com/albums/y180/method007/beforedeleting4.jpg ( values of customer table before deleting record)


http://i5.photobucket.com/albums/y180/method007/afterdeleting5.jpg

(values of customer table after deleting records)
-----------------------------------------------------------
my code
[code]


''' code for adding new record . This part run when the button is clicked

Private Sub Command14_Click()

If DCount("*", "Customer") = 0 Then
Me.customerno = 1
Else
Me.customerno = DMax("Customerno", "Customer") + 1
Me.customerName.Value = " "
End If


End Sub


'''' this part delets the current record
Private Sub cmdDelete_Click()

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
End Sub



[code]

View 2 Replies View Related

Tables :: Find Control Bounded To Field

Jul 13, 2013

I have:
1) a table (T) with fields (F1, F2, .... , Fk, .... Fn). I do not know "n"
2) a form (F) bounded to this table

I know that I have a control bounded to field Fk.

How I can find what control is ?

Something like this:

Dim MyCtl As Control
Dim ctl As Control
For Each ctl In Controls
If ctl IsBoundedToFieldFk Then
Set MyCtl = ctl
Exit For
End If
Next ctl

View 4 Replies View Related

Forms :: Having Two Combo Boxes Bounded To A Field Of Table

Jul 11, 2013

I have two combo boxes containing integers on a form. How can I store the values of these combo boxes in a field of a table separating them by comma or semi colon?

View 10 Replies View Related

Modules & VBA :: Default Property For Control Bounded To Date / Time Field

Dec 4, 2013

Access 2007

In a table I have 2 fields:

D1 , Date/Time , DefaultValue: Date()
D2 , Date/Time , DefaultValue: Date()+1

In a form (bounded to this table) I have, of course, 2 controls bounded to this fields:

txtD1 and txtD2

If the user change the value in txtD1 (using the Date picker) I like to change the DefaultValue for txtD1 to the new date and the DefaultValue for txtD2 to the new date + 1 day. Something like this:

Code:
Private Sub txtD1_AfterUpdate()
Me.txtD1.DefaultValue = Me.txtD1
Me.txtD2.DefaultValue = Me.txtD1 + 1 day
End Sub

I tried whatever crossed my mind... with the same "result" : #Name?

My Regional settings:

Short date format: dd.MM.yyyy
Date separator: Dot
Simple: 05.12.2013

View 14 Replies View Related

Controlling Autonumber Field - Start Autonumber From 1 Everyday (Composite Key)

Nov 21, 2013

I have two tables linked to each other in one to many relationship. Instead of auto number, the date and shift (Text) is being used as the primary keys (Composite Primary Key). Here is the tables structures,

Payouts Table:
Date: Primary Key
Shift (Day or Night) : Primary Key

Bills Table:
Date: Primary Key
Shift (Day or Night): Primary Key
Autonumber: Primary Key

The tables Payouts and Bills has one to many relationship. One payout row can have many bills. The problem is that I want to start the Autonumber in bills table everyday from 1. As date and shift are different for every day so even if i start bills from 1 everyday, it wont make same primary key. I can do it manually but I want to make it automatically.

View 4 Replies View Related

Custom Toolbar

Mar 29, 2006

HI all, I have recently created my own custom toolbar for my database which works fine on my computer, but when the database is transfered to another computer via a memory stick (needs to be done as it is going to be used on remote laptops away from the main network), the custom toolbar does not carry over. Instead of the custom toolbar being shown, the normal, main toolbars are reset to the default ones shown.

Is there a way of stopping the toolbars reseting when transfering the database so that my custom toolbar is the default toolbar?

Thanks for the help!

View 1 Replies View Related

Custom Toolbar

Jul 13, 2006

I have a custom toolbar in my application. However, it isn't visible at the top of the application window and when I try to make it so - via View - Toolbars - it doesn't appear on the list of available bars.

I had assumed that I had somehow deleted it, but I know it's still present somewhere, as when I try to recreate it, I get a message saying so.

How can I get the toolbar to be displayed? :confused:

View 8 Replies View Related

How Do I Create A Custom Msg Box

Jan 1, 2007

I have a database for call outs, i had a problem with double booking, but cured that, my problem now is every time my work mates try to book me in that go into the debug box (accidently) all i need is a ok button only msgbox to say the time is already booked. can anyone plaease help.

here is my db if any one want to have a look

View 2 Replies View Related

Custom Help File

Feb 13, 2007

Hi all. I have tried searches on here and via google but can't find a difinitive answer to my question. What i would like to do is create a custom help file that will run when the user requests it. I know there is a property option to set a custom help file within a form but i am unsure of the correct process to do this to get it working.

Could someone please help me understand what needs to be done.

Thanks in advance.

View 2 Replies View Related

Custom Toolbar

Mar 6, 2007

Hi folks:

when I create a custom toolbar on my db, would that custom toolbar be carried to other copies of my db? I'm hiding all toolbars using code and I need to display only one.

thanks

View 1 Replies View Related

Custom Menus

Oct 25, 2007

Since the introduction of 2003 when you create a custom menu you always get the 'Type in a question for help' tag and the min max close buttons. Is there any way of removing them or disabling them?
:confused:

View 1 Replies View Related

Custom Autonumbers.

Jun 17, 2006

I would like to create a custom autonumber to use as my primary key in one of my tables in my database.

I would like my records to be displayed as such C000001 , C000002 and so forth. Does anyone know how todo this?



Thanks in advance.

View 1 Replies View Related

Custom Relationship - Need Help!

Jul 31, 2006

Hello, I am trying to set up a relatively simple relationship between numeric fields in two (already related) tables. The problem is Access doesn't really support this type of relationship... or in fact, doesn't have an interface for coding your own custom relationships (if it had one, this would be ridiculously easy).

But it doesn't. So I have to turn to you.

To explain my problem better, I'll use a visual aid.

http://img269.imageshack.us/img269/9123/db1zx2.jpg

My two tables are as follows, the master is called [Raw Materials] and details is called [Raw Materials Details] (the aptly named).

I guess I should rename the [Raw Materials].[Stock] to something else (as this isn't REALLY data redundancy). But anyway, what I want to do is have the [Raw Materials].[Stock] automatically equal to Sum(Stock) FROM [Raw Materials Details].[Stock]. To rephrase, I want the Total Stock field to be automatically equal the sum of corresponding stocks.

Either way, I have no idea how to go about doing this. Can anyone point me in the right direction?

View 14 Replies View Related

Help Again With Custom Query

Aug 27, 2005

Hi all,

I cant make work, a query with creteria filled within controls of a form. I will be more specific to make u understand what i want to ask.
There are 2 text fields and 2 combo boxes. All of them call a report (by pressing a button) from the main table.
in the "totalquery" query i call each control this way:

select tblPeople.name,tblPeople.tel,tblPeople.age,tblPeop le.Car from tblPeople where tblPeople.Name=[forms]![frmMyform]![combo1] OR tblPeople.tel=[forms]![frmMyform]![text1field] OR tblPeople.age=[forms]![frmMyform]![text2field] OR tblPeople.Car=[forms]![frmMyform]![combo2]
GROUP BY tblPeople.name,tblPeople.tel,tblPeople.age,tblPeop le.Car;

I want to make it display (the right) results if one of the text filed/combo is filled or with data, or all together or in combinations.
i use the OR statement,but it doesnt work if I fill in two fields/combos together and etc.
Anyone? :rolleyes:

View 4 Replies View Related

Custom Queries

Sep 12, 2007

Hello all!

Basically what I am wanting to know is how difficult is it going to be to do the following.

I want to have 8 searchable fields, first name, last name, MO, Job code, etc etc. I want to be able to type somthing in any one of these fields and then when i hit a submit button I want to to return to me all the data that corresponds to what I am looking for. I know I could just create a simple query from the query wizard but i want everything in the program to look uniform as to not throw every one off. How difficult would this be and how should I go about doing it. If you need more information let me know.

View 2 Replies View Related

Custom Order By?

Oct 17, 2007

Wondering if it is possible to order by particular values??

That is I have a column (varchar) named "Status" with values such as; Entered, Active, Completed etc (I didnt design the DB).

What I need is to order by Entered, then by Active then Completed etc.. Is this possible??

Ive got this code for SQL; but it doesnt work in Access (03)..


ORDER BY

CASE Status

WHEN 'Entered' THEN 0

WHEN 'Active' THEN 1

WHEN 'Completed' THEN 2

ELSE 3 END, Status

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved