I have a field in a form that contained 8 characters long (Ex: ZWXX0001). The first 4 character always stay Constance. However the last 4 characters I'd like it to increment by 1 after each time user press enter to go to the next record when they do data entry. If this possible will save my user a lot of time. For Example: ZWXX0002, ZWXX0003, ZWXX0004 . Is this possible? Thanks in advance..
View RepliesI an trying to create a data entry form (IndividualsEntryFm) to input data for fields such as (First Name),(Birthdate) etc., these to be saved to the (IndividualsTbl)
I also have another table (NamesTbl) which has family names etc. The two tables are linked by a (MainID) field. I want a combo box on the individualsEntryFm so that I can select the family name. Then I wish the empty fields for the IndividualsTbl to be available to enter data.When I press the save button I then want this data saved, together with the MainID from the combo box to the IndividualsTbl.
I have set the IndividualsTbl with a (PersonID) field as an auto number each individual therefore has a unique PersonID but may well share the MainID. I'm trying to link many people to the same address.
How can I get the value from a field in one table (in the sub form) to copy/insert into a field in another table (in the main form) when adding a new record?The main form and sub form are linked using parent/child linking, and the sub form is in a tab.I have table A (Visit Dates) in the main form which is used to record the date of a visit to a church. Table B (Quarters and Peals) is used to record an event that took place at that church during that visit. Note that not all visits in table A require a record to be created in table B - but half or more do.
In tables A and B I have a field called "QuarterOrPealID" and these are both primary keys, though the field in table B is set to 'no duplicates' and in table A it's set to 'duplicates allowed', as table A has its own auto number/pk. They are both linked in the relationships.
So, when I add a new record to table A using the main form, I might then need to click on the tab in the sub form to create a new record in table B, which has to be linked to the same record in table A. When the "QuarterOrPealID" auto number/pk is generated in the sub form (table B), I need that value to update to the "QuarterOrPealID" field of the main form (table A), so that when I'm viewing these records the form pulls all the information nicely together.
I have a form that users will use to add new records (customers).
There is a field named VAT_Registration_no
First of all i want some code to check for duplicates in that field only,before entering the next field.If the record exists i want to show a msgbox and set focus to the vat_registration_no field. Also I want the same thing to happen if the vat_registration_no field is empty.here is what i have tried:
Private Sub VAT_registration_no_AfterUpdate()
Dim btest As Boolean
If VAT_registration_no = "" Or IsNull(Me.VAT_registration_no) Then
MsgBox "Please enter a Vat Registration No.", vbOKOnly, "error"
Me.VAT_registration_no.SetFocus
Else
btest = True
End If
End Sub
and to all other fields:
Private Sub textfield_Enter()
If Not btest Then
Me.textfield.SetFocus
End If
End Sub
If I just press enter to go straight to the second field I dont get a msg. If I write something and delete it and press enter i get the msg but when I press ok the cursor goes to the next field. I want it to go to the vat_registration_no field again. And I also want this to happen even if dont write something and then delete it.
Hi,
Is it possible to restrict data entry for some users only on one field?
Regards,
B
How can I set the default value equal to the value in the previous record?
View 1 Replies View RelatedHi,
I've join a small company who uses access to store a route card system. Unforunatly it's partially broken and nobody seems to know how to fix it. Instead of bodging it forever i'm hoping to fix it.
My access knowledge is fairly limited so please keep it simple
The route cards are fairly standard, the primary key beening the route card number, the are various fields material, order date, etc.
The problem is when you enter the data into 3 of these fields access repeats the data to all the forms. At the moment we're having to leave them blank and fill them in by hand, which means we don't have the data on computer.
Anyone know any possible causes for this?
Hello,
I have two tables which contain the following fields (simplified);
Table 1:
ID
Startdate
Enddate
Table 2:
ID
Unitdate
Treatment
The tables are related (one-to-many) through the ID field.
In operation, the user first enters the Startdate and an Enddate for a patients' hospital stay in Table 1. Following this, the user enters the Startdate again in the Unitdate field in Table 2 and selects a Treatment from a drop-down list. After this, the user enters the following in the Unitdate field and selects a treatment. This is repeated in consecutive order until the Enddate is reached.
The end result looks like this;
Table 1:
ID Startdate Enddate
123456 01/01/2005 04/01/2005
Table 2:
ID Unitdate Treatment
123456 01/01/2005 Ventilation
123456 02/01/2005 Ventilation
123456 03/01/2005 Haemofiltration
123456 04/01/2005 Ventilation
Does anyone know if there is any way to restrict the Unitdate field in Table 2 to only allow dates to be entered that are between the Startdate and Enddate fields in Table 1? Ideally, I would like the Unitdate to populate automatically with the dates, starting with the Startdate value and ending with the Enddate value and filling in all the intermediate dates. Also I would like to make sure that there is no way of having duplicate dates in the Unitdate field.
If anyone can help, I would be extremely grateful.
Best wishes
Russell
Hi,
I've got a data entry form where orders can be created. Within this form there is a ComboBox which is used to search for items and stores the selected item ID in the Order table. However the field for storing the Unit Price relating to the selected item is currently manually enterable. Where as I would like this to be dependent on the item selected within the ComboBox and updated automatically.
At the moment I've added an extra colum to the ComboBox to retrieve the relevant Unit Price and I can show this in a normal seperate text box using '=Combo27.Column(1)' as the ControlSource.
How can I get rid of the text box and incorporate the code into the current UnitPrice field so as it inserts the relevant record (price) into the UnitPrice coloumn of the Order table?
Any help on this would be much appreciated!
I have a field in a table that keeps rounding the data entry off. It has identical properties to the field next to it but one rounds and the other does not. I tried deleting the field and creating a new one.
View 5 Replies View RelatedI have a form, combo box selection, when i make a selection, all the info is good on all my tabs, which holds the subform.My issue is, on one of the subform, it's a data entry form, where I want it to be on a new record page. instead it list all records. I want my users to see a blank page, but have one field populated. is this possible?
1) blank data entry page, but with a populated field?When a CU Name is selected,I want the (Enter Note) tab to go to a data entry or last page or new page.I have tried putting it to data entry form, but the CU Number will not populate.
I have created a basic Volunteer Events and Hours Volunteered tracking database.
I created a form Volunteer Hours to allow an end user to use that form to record the volunteer and hours volunteered on a specific Event. There are two tables needed in the form Volunteer Hours to be used for the end users to record the Event, Volunteer, Date and Hours. The form has drill down fields from two tables:
Table A - Volunteer Events (event name, date, descr, owner, co-owner, loc, status)
Table B - Volunteers (name, co, div, emp#,title)
I want to be able to select the Volunteer Event and have the event date automatically populate in the Volunteer Hours form date field when the Event is selected to eliminate the end user from having to enter the date in.
Also; I would like to be able to add more than one volunteer at a time to an event on the form, this would also decrease the data entry time.
Are one or both of these things possible?
I have a mainform set to data entry to add records to TABLE1. I inserted a subform that shows fields from TABLE2. I have a field in TABLE2, let's say TBL2FLD1(NUMBER). I want to add TBL1FLD2(NUMBER) from TABLE1 in the mainform to TBL2FLD1 and update said field in TABLE2.
My problem is:
(1) I get a #TYPE! error when I try to add the two fields, which I have tried many, many ways (adding them together in the Default Value; creating an unbound field to hold the calculation and then putting that field into the Default Value of TBL2FLD1; ETC), and
(2) I don't know how to get the subform to update TABLE2. It seems like the control gets stuck in the subform. Is there a way to accomplish this without coding it?
I have a form for entry and some fields are computed or result of a query from another table. I have a function that looks up a value from another table like so
************************************************** ********
Public Function GetTargetType() As Variant
GetTargetType = DLookup("type", "tblFormulations", "[tblFormulations!formulation]=Forms![frmNmsConsumptionEntry]![formulation]")
End Function
************************************************** ********
Which works fine when I test in the immediate window.Then I have this form event. This however does not insert this value when I am adding records using my continuous form.
************************************************** ********
Private Sub Form_BeforeInsert(Cancel As Integer)
Me!target_group = GetTargetType()
'Forms!frmNmsConsumptionEntry!target_group = GetTargetType()
'[tblNmsConsumption.target_group] = GetTargetType()
End Sub
************************************************** ********
making sure I can insert this value once retrieved.
I have one field AccountName in customer table and another field AccountID.
In my form I would like to select from the combo box AccountName during data entry and then have the AccountID automatically update in the Account ID field.
My situation is this. I have 3 tables that I have imported from my mainframe system, between these 3 tables I have the data of product code, description,supplier code, supplier name, order method, and ABC code.
I am trying to create another table that I can capture daily Out Of Stock data for products.
What I would like to do is to enter the product number in the first field of my new table, and then the remaining fields will auto populate with the correct details based on the product data stored in the parent tables that I have imported.
How can this be done?
i have 2 field "CLIENT ID" and "CLIENT CODE". when i post a new record, i want the "CLIENT ID" and "CLIENT CODE" to merge to give me 1 field "CLIENT". the CLIENT CODE should be prompted for eg. THO or BLT and added to the CLIENT ID which will compose of 001 or 002. The CLIENT ID should also increment when each new CLIENT record is posted.
The example should make things clear.
CLIENT ID CLIENT CODE CLIENT
001 THO THO001
002 THO THO002
001 BLT BLT001
it should look up the last CLIENT value and then for the new record just continue on the previous entry.
Hi
I have a numeric field called FileNo and an autonumber field called FileID in a table called tblFile.
I also have a lookup numeric field called FileTypeID (with values 1 or 2 or 3) on another table called tblFileType.
I need the value of FileNo which I am showing on a Form frmFile to change dependant on the value of the FileTypeID
i.e. If FileTypeID = 1 FlieNo should start at 100
If FileTypeID = 2 FileNo should start at 200
If FileTypeID = 3 FileNo should start at 300
Then when I create a new record I need to increment by 1 the value of FileNo according to the FileTypeID
I've setup a button and attached this code to its onclick event but it only works as long as I don't change the FileTypeID
Dim B As Integer
Dim H As Integer
Dim N As Integer
Do While FileID > 0
DoCmd.GoToRecord,,acPrevious
If FileTypeID = 1 Then
B = FileNo
Else
If FileTypeID = 2 Then
H = FileNo
Else
If FileTypeID = 3 Then
N = FileNo
End If
End If
End If
Loop
DoCmd.GoToRecord,,acLast
If FileType = 1 Then
FileNumber = B + 1
Else
If FileType = 2 Then
FileNumber = H + 1
Else
If FileType = 3 Then
FileNumber = N + 1
End If
End If
End If
ViRi
Here's my query:
SELECT units, nation, unique_brigades,
Switch ( [unique_brigades]='A', 2000, [unique_brigades]='B',3000, [unique_brigades]='C',4000) AS brigade_number,
unit_urn_desc AS urn_desc
FROM units
What I'd like to be able to do is increment the values (2000,3000,4000) by 1 for each new row. Does anyone know a way to do this?
I have a table with order numbers I would like to auto increment the number part of the order when new record is added to the table.
My order numbers looks like this:
UZSK-0001
UZSK-0002
UZSK-0003
UZSK-0002
How to make access increment the number part?
I have a table called tblProducts. Within the table I have a field called versionNumber. I want to update the version number to increment by one each time I run a query.
So if the version number is 1 after running the query it becomes 2. So far I have done the following:
CurrentDb.Execute "UPDATE tblProducts
SET tblProducts.versionNumber = "Need to increment by one, don't know how to"
FROM tblProducts;"
Please advice me how to implenent the Update query.
Thanks,
Ket
Basically, we have different camera modules on which we perform a given test a number of times, giving rise to 2 tables, tblCamera which will store basic info about the module and tblTest which will hold info for each test perfomed.
The camera module has a unique ID (CameraID, the Primary key in tblCamera and foreign key in tblTest (one to many)). The primary key in tblTest will be a compound key of CameraID and an incremental number for each test performed on a given camera (TestNo). The data for the tests will need to be entered through a form, so I can build a form based on tblCamera with a subform based on tblTest (which would be in datasheet view)... What I am looking for is a way to increment TestNo in the subform, starting at 1. I have tried using
=Nz(DMax("TestNo","tblTest","CameraID = '" & [Forms]![frmCamera]![txtCameraID] & "'"),0) + 1
in the Default Value box for this field, which works to a point but has the curious effect of adding each number twice (as the default value for a new record seems to be added as soon as you start typing in the current 'last row')
I just can't seem to get my head around this one, any suggestions would be much appreciated...
Thanks,
Bogzla
Statement below will increment a numeric field by 1 without using Autonumber.
[Invoice Number] = Nz(Dmax("Invoice Number", "Invoice Transaction Header")) + 1
This was to be applied Before Update under the event of Invoice Number.
I have a form that is being used for sample submission. Ideally, the user will be able to put in some information (3 or 4 fields) and the number of samples that they're taking, click the arrow and it will insert that number of samples into a temporary table that is then displayed on a continuous form. From there, they can edit samples, add more samples, and generate a report to submit to the genotyping group. Once their sample list is final, I will append it to the master list and clear the temporary table.
I've got the basics working. A looped "INSERT INTO" using a counter inserts the relevant information. I cannot, however, seem to get the numbering field to work.
Ultimately, the numbers need to look something like MySample-001, where MySample would be one of the other fields. Incrementing the 001 is the problem. In another situation in the database, I can successfully use DMax+1 to assign a single number on a single form, but I can't get it to increment the sample numbers.
Ideally, I'd like it to happen when the form is generated. However, I'm also open to having them assigned when the sample list is finalized, before inserting into master table.
I've got most of what he wants sorted but this last task I am completely flummoxed. All of his projects are allocated an ID (named Project Reference), starting from P010010 and increasing by 1 each time. I've made a form that allows a new project to be recorded by entering all the details and hitting the 'record' button, but he wants the Project Reference field to be automatically filled in each time (understandably), increasing by 1 from the last record.
So if the last record was P010311, then when the form opens the Project Reference should automatically be P010312.
I've looked into this and found many guides talking about DMax and DIM and strCriteria and whatnot, but no matter how many of them I follow and try to adapt to my own database I can't get it to work at all.
The table the ID comes from is called General, and the field is Project Reference. The ID should automatically be filled into a text box called txtRef whenever the form opens and a button to add a new record is pressed, being 1 higher than the previous ID.
I have inherited an Access DB that is supposed to be kept "alive" but not made much better. On a weekly basis I get an Excel CSV that I'm supposed to import into one massive table; that works just fine.
However, there is another query that takes values from a table named "New Users From Import Table" and appends them to the current "User" table.
But before running that query I have to manually update a field called "Display Name ID" with the next highest number from the User table but only the highest value that isn't in the 9000 number range.
For example, my next highest "Display Name ID" is 1144, which of course does not include the records that range between 9000 - 9008.
I had to manually enter 89 "Display Name ID" values yesterday and I know there has to be a way to update that field when I pull the data over from the temp table without having to do it all manually.
Here's the query that pulls the data into the "User" table (after I've manually entered the incremented values).
INSERT INTO Users ( [Display Name ID], [User Type], Organization, [Display Name], [Alias Name] )
SELECT [New Users From Import Table].[Display Name ID], [New Users From Import Table].[User Type], [New Users From Import Table].Organization, [New Users From Import Table].[Display Name], [New Users From Import Table].[Alias Name]
FROM [New Users From Import Table];