473,327 Members | 2,069 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

When entering a record, check to see if exists, if so edit, if not add record...

I am new to access, am building a DB for a lab, the DB uses a switchboard that opens full screen to different forms with only options to add a new record. I have different forms for each test and about 80% of the data entered is just info on the item. The problem is, the way I currently have it setup I can only add one record (because of Primary Key). The item number being tested is my primary key, and then other info on the item.

What I would like to do, but don't know how....

The item number is the first field on each form I have and the first 15 fields or so are the same on each form. I would like to enter the item number, then tab to the next field. Upon the tab stroke, have that field check the database to see if that primary key is already recorded. If so, update all other item fields, and allow me to only update the testing fields. If not, allow me to continue adding the item info. We do several different tests on items, sometimes only one test per item, sometimes many per item with results coming in at different times. I never know the item I am entering is in the DB until I enter the item and then get a message the record can't be updated because it would duplicate the primary key. Everything seems to work correctly on the DB if I only have one test per item. Any help is appreciated. Thanks.
Feb 14 '14 #1
6 5655
mcupito
294 256MB
This is why autonumber fields are good for Primary Keys.

You can run a query to check If "ItemNumber" Is Null (Or Is Not Null may better for you), and change the visibility of certain objects.

Apart from that, I'm not sure what you mean by "update all the other item fields"

Also, why are you not just searching by an "ItemNumber" and if you need to enter new information, you do it on another form?

You could create a query that returns the DMax() of the ItemNumber and display it in a textbox or something, so you know what the most recent record you entered is.* If that does not work, you could try the same with a Last() function. Nonetheless, a search feature would still seem more sensible to me.

*This only works if the Item Number is increasing. If the ItemNumber is just random integers, this won't work.

That error you're getting regarding a recurring primary key is the result of bad database design / usage. You should never have to "guess" a primary key to create a new record.

I made some assumptions in my comment, but I wanted to clear them up briefly. We have no idea what the ItemNumber field is, we have no idea how often you're entering this information, and have no idea how your program functions. A little more clarity in your question would help solve these issues, so we can understand your situation a little more.

The basic understanding of your question is that you're committing to trial and error every single time you want to enter data. That shouldn't be the scenario..
Feb 14 '14 #2
Thanks mcupito, but I think I should explain more. I know the auto number would "kind of" fix my problem, but the record would not contain all the info in one record. Because of that, the DB would become larger than needed, because I would have several records with duplicate information about the item being tested.

I know I can run a query to see if the record is already entered, but I'm trying to set this up for people that can barely check email. The are not stupid people, just not very computer literate.

I know about the error I'm getting and why I'm getting it and this is the reason I'm asking for help. I'm not guessing at a primary key, I simply don't know if someone has already entered a test for something on it before. Your may be correct, my database may be a bad design, but it's the best way I could design it with the knowledge I have.

Let me show you some screen shots of what I have, so you can better understand.

Switchboard (edited from my original):



One of the Forms (edited somewhat from original)



The user will start the program at the switchboard in full screen, select the test, and enter the info in the form.

What I would like to happen...when a roll number is entered, check to see if it already exists in the database, and if so fill the fields pertaining to the roll information in down to the test fields, the user can enter from here, and allow me to add the new data to the roll record. All this info would exists only if this roll was tested under a different test prior to this test.

I really don't care if it updates the other fields or not, as long as it checks the DB and allows me to update the record if it's already in the DB, or add if it's not currently in the DB. The update other fields is only a to help the end user.

Thanks for any help...
Feb 15 '14 #3
Also, I forgot to mention. I test up to maybe 100 rolls a day, and depending on the testing required, it may take up to 2 weeks to get all the test information back per roll. We could do 10 or so test per roll. That being said, the same roll could come up everyday for two weeks under a different test method (extreme), but you get the point. I have different people that would be updating the records at different times and updating different test results.

For most of the rolls we test, we would only be doing one test per roll. It's the 10% that is hurting my DB.
Feb 15 '14 #4
mcupito
294 256MB
An autonumber and the ItemNumber can both be the primary keys for one table. (I believe). It's the combination of both of them that makes them unique.

You also need to make sure you're using a continuous form (judging from a Screen Shot a DataSheet is out of the question).

Is all of the data that you're trying to show located in one table? what is the data source for the form where you're entering data? Is it the table where the data is located?

I'm asking because it is important to know what fields are on the form you're entering. I am assuming they are all in one table, is that correct?

I would still recommend a "Search" by roll number in conjunction with an "Add new roll number" function.

The search gives the users the ability to see if the roll number exists. From there you can then determine if the fields need to be filled in or not. If not, you would be adding a new roll number (item # ?)

I hope I am making sense. I'm not sure if a combobox would be logical in scenario because I'm not sure if it can allow new additions? Though, I guess in theory you could code behind a combobox, something like:

Expand|Select|Wrap|Line Numbers
  1. Case 1 search for item information
  2. Case 2 Add new item
I am also not THAT familiar with populating fields on forms from basically a "search if exists" query and bringing back information that has multiple entries. That is a wrench in and of itself.

Let me try to explain a little bit more: If you have multiple tests for the same item #, which is what you said in your OP, how do you know which Roll# to add test information to? You have not mentioned how the Test forms differ.

Are you updating information, or adding a new record when you enter "new test results" and the Roll# has been found to exist? In your post you say "I have different people that would be updating the records at different times and updating different test results."

If you're updating, you DEFINITELY need a search function. Why go in blind when you could have perfect vision?
Feb 15 '14 #5
mcupito, After searching, I came up with the following code that will check if the data exists and give me a message, this is one key to my puzzle.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Roll_Number_BeforeUpdate(Cancel As Integer)
  2. If DCount("[Roll Number]", "Table1", "[Roll Number]= '" & Me![Roll Number] & "'") > 0 Then
  3. MsgBox "Roll Number Is Already In Database!"
  4. End If
  5. End Sub
Now, when this happens, instead of the message, I would like to populate the form fields from the information from my table and allow me to add new data to the roll number record.
Feb 16 '14 #6
mcupito
294 256MB
You would need a

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenQuery "YourQueryName" 
Your query would need to return the rows with the Roll#, and you would need to use the results from the query and put them in the controls on the form. (Text boxes, combo boxes, etc.)

If the DCount() > 0 (meaning the record was found)

My problem is - aren't you saying there are multiple records with the same Roll#? (Different tests for the same Roll#?) How do you know which record to update?
Feb 16 '14 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Frans Bouma | last post by:
Hello, It seems VS.NET 2003 locks up itself and the complete shell (mouse locks also) when entering a breakpoint in a special situation. Below is the code to reproduce this behavior. It...
2
by: Mike N. | last post by:
Hello again.. I have a table with a text field named "Requisition" (Indexed, no dupes). It is not the key field. This field is the first text box on a form. I would like to check to make sure the...
2
by: Rich | last post by:
Hello, I need to trap/detect when a textbox is entered via the tabkey. If the textbox is not empty when entered via the tabkey then set focus to next textbox. To enter that textbox would then...
5
by: Hooyoo | last post by:
Hi, here. I write following codes: string password = Console.ReadLine(); I want users enter their passwords, but readline will show content of password when entering, so is there any way to...
1
by: alex.gruenther | last post by:
I am new to Access and need help creating an edit record form. I've got a table called Player with attributes like name, address, phone, etc. I've created an edit player form with a combo box...
2
by: Ranma13 | last post by:
Hello, I have a duplicate record check written in VB for a check in/check out database. Here's the pseudocode, written for the BeforeUpdate property on the form: If DCount(search for records with...
7
by: djpaul | last post by:
Hello! I want to check if a record exists in the last 30 records that were added. For example, i have a table wich contains about 34000 records by now. But i want to check if the songID is in the...
11
by: prashantdixit | last post by:
Hi, I am developing a stock control software. Iam stuck somewhere. I have a form "Add new stock" consisting of combobox, text boxes etc. which is used to add records in a table. I have another...
3
TheSmileyCoder
by: TheSmileyCoder | last post by:
Im using an imagecombo in my form, and I have its Text property set to "Please Select". The text property of the imagecombo is what is displayed when you have not yet made a selection, kinda like...
0
by: jagdishgore | last post by:
redio buttons values fetching from table to edit record or update records, cmd.Parameters.AddWithValue("@user_receive_mails", c); cmd.Parameters.AddWithValue("@user_receive_calls", d);...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.