Hello
I am trying to figure out how to make my database check to see if the primary key value is a duplicate of anything ive entered previously and if yes, for my database to bring up that record instead of adding a new record.
I know how to do bring up records using a separate combobox (find) but I cant seem to do it using the primary key field itself.
Many thanks in advance.
I have a form with subforms, where the main form's source is a table called tblCharts. Within this table there is a field called ID that is autonumbered.
So I created a combo box that's row source is a query that selects the ID field from table charts and two other descriptive fields to make navigation easier. Limit List is set to yes.
The problem comes up with code that I've used multiple times, but not with an autonumber. The code typicallly looks like this when I'm searching on a text field.
Code:
Sub cboPatientSelection_AfterUpdate ()
Dim rs As DAO.Recordset
If Not IsNull(Me.cboPatientSelection) Then
If Me.Dirty Then
Me.Dirty = False
[Code] ....
This worked at first but then 3070 runtime error came up, the form started messing up. So I made this code
Code:
Sub cboPatientSelection_AfterUpdate ()
Dim rs As DAO.Recordset
If Not IsNull(Me.cboPatientSelection) Then
If Me.Dirty Then
Me.Dirty = False
[code].....
This code worked at first, so I decided to make a copy of my form called Copy Of frmCharts. After this the code stopped working on the initial frmCharts but now works on the copy.
I also received other errors besides 2070. I know what error 3070 is but I can't figure out why it's not working on this form. I closed and opened the form a few times and it happened to start working. Very frustrating when I'm trying to make the system reliable for others to use
The code only seems to do this when I enter design view then switch back to form view.
I have a table (tblSales) with these fields (RecDate, Code, Type, OrderCount).
I also have a linked table (lnkSales) with these fields.
Daily I append the data from the lnkSales to tblSales.
The tblSales table must have a record for each code daily.
These are the codes (01,02,05,07,09,10,15).
I need to automatically add a record for each code that wasn't appended.
For example,
lnkSales contains:
03/22/06 01 Mc 3
03/22/06 02 Mc 1
03/22/06 05 Mc 1
03/22/06 07 Mc 2
03/22/06 10 Mc 1
When appended to tblSales there is no record for code 09 or 15.
I need to add these records to tblSales
03/22/06 09 Mc 0
03/22/06 15 Mc 0
Can someone explain the best way to accomplish this?
Thanks.
I have a table (tblSales) with these fields (RecDate, Code, Type, OrderCount).
I also have a linked table (lnkSales) with these fields.
Daily I append the data from the lnkSales to tblSales.
The tblSales table must have a record for each code daily.
These are the codes (01,02,05,07,09,10,15).
I need to automatically add a record for each code that wasn't appended.
For example,
lnkSales contains:
03/22/06 01 Mc 3
03/22/06 02 Mc 1
03/22/06 05 Mc 1
03/22/06 07 Mc 2
03/22/06 10 Mc 1
When appended to tblSales there is no record for code 09 or 15.
I need to add these records to tblSales
03/22/06 09 Mc 0
03/22/06 15 Mc 0
Can someone explain the best way to accomplish this?
Thanks.
Hi,
To make a long and tedious story short:
Does anyone know why Access (2003) creates an index called PrimaryKey and an index called User_ID when I'm in the table design view and ask it to make the User_ID field the primary key?
After years of database development I've only just noticed this. Not normally not a problem but for this:
Use ADOX to find the field name of the primay key for a table. I took the code right out of a Access 2000 developers handbook. It creates a collection of the table indexes and steps through them one at a time checking if the PrimaryKey property is true. It hits the index called PrimaryKey and says 'yes it's true, this is the PK', I then go on to use the index.name property in an SQL Select to create an ADO recordset. The only thing is that the index called PrimaryKey is not a field name and the field name User_ID is not a PK :eek:
I go into the indexes list in table design and delete and re-create the indexes and PKs as I want and the code works fine.
So, again, why does Access do this? I'm so confused! :confused:
Hello all,
I have a form that looks at my "master table" where users put in general info about a sheet of material.
There are two combo boxes on this form, Batch# and Lot#. They will use the same Batch# and Lot# for many sheets. Every time a new batch and lot are entered, this will bring up a pop-up form where the user needs to take a measurement of that batch and lot for QA. This will go into a different table, where the batch and lot #'s are unique(many of the same batch#'s with different Lot#'s).
After the lot# is entered, I want to run a query in VB that looks for those unique identifiers, and if it doesn't find a record, my pop-up form will run.
This is probably something very easy....... It was a very long weekend and I'm tired of thinking.
Thanks
Scott
Hey all,
Tables
ProductsProductBrandSize
PurchaseDetailProductPriceQuantity
Forms
frmFoodSub
Combo BoxesiProductiBrand
TextboxsiSizeiQuantityiPrice
For a couple of days now I've been trying to devise a way to achieve what I want, but I just keep going in circles and hitting errors (thanks to forum members, I've been able to solve most of them.) So, here's what I want to be able to do:
Input a product using the iproduct combo box (which gets its list of values from the Products table); its brand, using the ibrand combo box; and its size using the isize textbox, all in the frmFoodSub form. I then want it to check to see if an exact record already exists (ie. the same product, same brand, same size.)
If it doesn't exist, I want to create it. If it does, I want to do nothing. Then I want the product - either the one I just created, or from a record that already exists matching the iProduct input - to be input into the product field of the PurchaseDetails table. I then want to use iQuantity and iPrice textboxs, already linked to the PurchaseDetails table, to input the newly added product's quantity and price.
I hope my explanation was clear enough. I'd appreciate any help with this you may be able to offer.
I have created a form which enbles users to enter data into the fields which will add a new record to my table. What I am trying to do now is to create some kind of validation rule that will check whether the record already exists.
I need it to work this way; there are four input fields- 3 text and 1 is a date filed. When the user enters a new record they are not allowed to create a record that conatins the same combination of values. So if one record has for example filed 1 = 1 field 2 = abc field 3 = def; then no other record can have the same combination of values.
If the user enters an already existing combination there would be an error message saying which field is incorrect. If the combination doesn't exist it would add the record and display a message that the record was successfully added.
How can I do this?
I have a form, which has a combo box which a user can select an employee - the combo has three columns (Employee_Number, Surname, Initials) although the Employee_Number column is set at a 0 width, so not visible.
The form is bound to a query which only shows records that are 'Active'
When a user selects and employee in the combo box, I want the AfterUpdate event to check and see if there is already an active record - if there is, throw up an error message and then reveal a couple of buttons giving choices what to do next; if there isn't an active record, reveal three textboxes and then populate those textboxes with the values from the three columns from the combo.
The bit that is sticking me is the search - - I have tried DCount, but can't seem to get it to work
Here is the section of code that I am battling with:
Code:
Dim EmpNo As String
EmpNo = cboEmp_Check.column(0)
If DCount("Employee_Number", "Incomplete_Training", "Employee_Number=" & EmpNo) > 0 Then
MsgBox "Existing", vbOKOnly
I am trying to check if a record exists in a table.
Dim cert As String
Dim existingRec As Variant
existingRec = DLookup("[Certificate]", "[Fire]", "'cert' = 'cert'")
If existingRec = Null Then
Else
MsgBox "The Certificate " & existingRec & " is in the database"
End If
Note - Certificate is a column in the table "Fire"
Problem is that existingRec only finds the first value in the Certificate column, How do I get to check the entire Certificate column?
table A has 10 records where 1 field is set as REQUIRED/UNIQUE. the table B does not have any record but have a same field like table A. what I want is that if user insert the record in table B it should first check whether record is already present in table A or not, if already exists it should not accept insertion. and if not already there in table A it should insert record in table B.
View 3 Replies View RelatedOkay,
I have 33,099 records in a query, that i'm importing into a table. (don't bother witht he semantics, it's from a linked dbf file)
The table does not have a primary key. Given Three Fields (out of 74):
Item_ID
Title
AltTitle
With the table populated with all the records, I highlighted those three fields in Design View of the table, and told told access to make all three of them the Primary key. Upon attempting to save the table, I got an error message saying that data in the table violated the primary key unique fields rule or what not.
So I wanted to make a query to determine where the error occurred. I could not off the top of my head figure out how to select only the duplicated records in a table, so instead, i figured if they violated the Primary Key unique field rule, there should be duplicate entries. so I did this:
select distinctrow item_id, title, alttitle from tbl_Table;
I got 33, 099 records returned on the DISTINCTROW. Strange as that was, I deleted all the records from the table, set the primary key as I wanted it, and then repopulated the table via my sql insert into commands. This time the table reports only 33,093 records, meaning 6 records somehow violate the primary key unique index, but don't violate a DISTINCTROW call. How can i find them to determine how they are violating the primary key unique index?
thanks
Jaeden "Sifo Dyas" al'Raec Ruiner
hey again, i usually don't post this much and prefer to work it out alone but i have no idea why this isnt working (and the error im getting)
below are screen shots, i am trying to make an improved booking system, but i don't know why its saying i have a null value (i went through and made sure all fields are filled, in all related tables just to make sure)
http://i71.photobucket.com/albums/i136/King-b-/QueryPrimarykey.jpg
http://i71.photobucket.com/albums/i136/King-b-/QueryPrimarykeydesign.jpg
Is your BookingID set to autonumber? If it is an autonumber, where does the B come from?
no i have them all set as text with a inputmask of B000 (so booking numbers have to start with a letter (b) followed by three numbers)
(i edited and made new post as had a vague, uninteresting title)
What is the best way to warn a user if a record already exists?I've looked at some examples that use an SQL SELECT statement with a recordset to compare existing records against the one the user is entering but i'm not sure which event this should be triggered by - eg; would it be the control's 'After Update' ,on 'Dirty' or 'Change' event? Would the record have been saved when the 'After Update' is fired- if not then it would not be found in the recordset and the SQL would not work....
how to create a textbox function that automatically searches and completes the textboxt based on exisitng records (like the cell autocomplete feature in Excel)? This could then populate the form with the existing record fields (if found ) and the user could update the record if necessary?
I have a table with a 3-part primary key. Have a form with 3 unbound controls corresponding to the 3-part table key.
After the 3 form fields have been entered, I want to search the table to see if a record with the same 3 fields on the table exists. If it does I want to go to another form to enter data for a related table carrying forward the 3 key fields.
If it doesn't I want to go to a different form to keep the 3 fields already entered from form 1 and add additional fields to complete the record.
I am trying to check if a record exists, but I keep getting a error!!!
Private Sub Command10_Click()
If DCount("*", "tbl", "[ID] = "") <> 0 Then
MsgBox "This record already exists.
Else
stDocName1 = "McrAddNewRecord"
DoCmd.RunMacro stDocName1
End If
End Sub
I currently use
Code:
strSQL = "INSERT INTO tblDepartments (Department) VALUES(txtnewdept)"
To insert new departments into a table, however id like it to check to see if a department name exists in tbldepartments.department to prevent duplicates being added?
I have a database that involves a lot of data about ppl and I'd like to prevent duplicate entries.
I have a form that gathers data. I'd like to have a button after name, surname and birthday are written.
A click on the button would display a msgbox that says that person already exist, maybe even with the added - edit the old entry/add new record anyway/abort options.
Hi,
In the current db there are some tables and queries, forms designed around them
With one table Test
http://members.optusnet.com.au/~lukechang/access/table_d.JPG
That has 3 records in them
http://members.optusnet.com.au/~lukechang/access/table.JPG
Just won't show in form anymore, at this stage
http://members.optusnet.com.au/~lukechang/access/form.JPG
The Form was working previously. This is the second time this problem has came up, I thought it would go away if I made a new form and pasted all the controls and codes. It indeed went away for a while until this problem hit me again...
This db is in Access 2000 format and designed using Access 2003
I would appreciate if anyone is willing to point me to the right directions, many many thanks in advance
The problem I have is, that I need to insert an apointment into de database but first I need to verify if there is a record or an apointment in that room that day the same hour, if that is so send a msgbox saying an apointment already exist in that room this day at this hour.
i tried using dlookup but it only works in one record using just one criteria
I have 2 tables:
Query_Rates (Actually this is the result of a query):
Unit
R_Date
Sold_Rate
A
24-AUG-15
145
[code]...
So what I want is a list of all the records from the Query_Rates table where the absolute differences between the sold rates between Query_Rates & [Sent till date] (matching the unit # and the dates) is greater than 1 and the record shouldn't be displayed if it is already present in the [Sent till date] table. But if you notice the first record which has unit A is already mentioned in the "Sent till date" table and shouldn't be repeated again in the query result.
The desired out put should be:
Unit
R_Date
Sold_Rate
X
25-AUG-15
200
I'm tracking the holiday entitlement of a team of people. I use a query to work out how much unbooked holiday they have to take.
My problem is where I'm scheduling next year my query returns the names of those who have booked a holiday and their remaining entitelement. That's as it should be. However if someone hasn't yet booked any holidays then it simply doesn't display their record. I would like it to treat that record as zero and show the remaining entitlement as a full years entitlement.
Here's the SQL
SELECT Employees.Trainer_Name, Sum([2015 Holiday].[2015 Days]) AS [SumOf2015 Days], Employees.Holiday_Days, [Employees]![Holiday_Days]-[SumOf2015 Days] AS 2015
FROM [2015 Holiday] INNER JOIN Employees ON [2015 Holiday].Trainer_Name = Employees.Trainer_Name
GROUP BY Employees.Trainer_Name, Employees.Holiday_Days;
The problem here is that the Sum of 2015 holiday is Null
Do I somehow need to create 0 hours records?
I am trying to achieve the following - I want to query a table to see if a record exists with a particular field blank. If so, I would like to prompt the user for data.
In real world terms, when assigning an item to a user I would like to first make sure that the item is not already assigned to somebody else. I have 4 fields, UserName, Item, IssueFrom, IssueTo. So when an item is assigned to a user, the first 3 fields are populated and the IssueTo remains blank, until that item is assigned to somebody else.
At the minute I have nothing in place to prevent a user from assigning the same item to multiple users and having multiple records for the same item in the table.
I have a table in Access that I have a form saving new records to. Before this save occurs, I would like Access to check if the account number already exists and if the account does exist if it is outstanding. If both of those conditions are met I would like a message box to display and cancel the save as it is a duplicate. I can't seem to get it to work though.
I was thinking to use a filtered recordset based on one of the conditions and then perform a find on that recordset to see if it is null.
Code:
dim acct as long
dim rstfiltered as DAO.Recordset
Set rstfiltered = CurrentDb.OpenRecordset("SELECT * FROM tblclstrack WHERE [Request Status] <> 'Completed'")
acct = Me.cd_number.Value
[code]...
Hi guys...got a problem here....
I got a query which joins two table....
Individual
fid1
fQSname
fQSRegno
Point
fid2
fQSRegno
fQSPoint
Tables join using fQSRegno.
Scenario is
Tbl Individual contains all the members info.Tbl Point contains point given to members who attend courses. Problem is when I created a query...
Query1
fid3
fQSname
fQSRegno *from tblPoint
fQSPoint
the data shows only members who got points. Is it possible to include member who don't have point like new registred member to show in this query....
TQ
Okay I have 3 forms. I have one form which is called frmEndOfTrial on this form there is a combo box which has two values--> Accepted and Return for credit.
I have two other forms called frmAccept and frmRFC. I have dragged those two forms onto the frmEndofTrial form and set there visibility to No.
What I want to happen is that when the user clicks on the 1st or 2nd option that corresponding form opens below the combo box?
I've tried a few things here but can't get it to work. I'm pretty limited in knowledge on access. Right now on hte combo box events I have this code for the After Update event
me.sfrmRFC.Visible = True
me.sfrmAccept.Visible = False
I know this is not what I want and not finished cause if I click on either accept or RFC it just brings up the sfrmRFC. How can I do this???