Discuss "comparing records" in the Microsoft Access forum.

I have a form, where i assigning computers to peoples

so 2 persons cant have the same computer

so i need to now, how to make this

if i put a serial number, and someone already have that serial assigned

that a message box appear and tell me that, that serial is already use, and by who

how can i do that??

To answer your question, we need to know what fields are in your table(s). Do you have one table that contains both the person's name and the computer number, or do you have 2 tables, one for persons and one for computers, etc.? There are a number of ways to do what you want to do, but they will be different, depending on how your data is organized.

i have everthing on the same table!!

because in that table i record, all the history of who have a coputer!!

So you have something like this?One simple thing you could do is just make the ComputerSerial the Primary Key, which would insure that there can never be more than one record with the same number. If you try to put the same number in another record, it would cause a MySQL error, which you could test for in your code and echo a message to the user. This method will not work, though, if you might have some people in the table who do NOT have a computer, because you cannot have Nulls (blanks) in a Primary Key field.

While a simple, one table design may possibly work for your purpose, it is poor database design, because there are really TWO entities represented: PEOPLE and COMPUTERS. In proper database design that requires at least TWO tables. With a properly designed database, questions like this are easy to answer. By putting both entities into one table, you have made it impossible to use the standard techniques of relational databases to achieve what you want, without writing a lot of unnecessary code to test for the condition you are trying to establish.

I have all the info of the people in one table, and the info of the computers on another one

and in one table, i chose, wich people and what computer

a friend told me to use dcount, do you have an example?

Below is a basic DCount syntax:
expression is the field that you use to count the number of records.

domain is the set of records. This can be a table or a query name.

criteria is optional. It is the WHERE clause to apply to the domain.

However, DCount is a Domain Aggregate Function and these can be somewhat slow.

__________________
jmurrayhead
If you agree with me... click the icon!
If my post solved your problem, click the button in the lower right-hand corner of the post.

Join our Folding team: DeveloperBarn Folding

I wouldn't use a dcount function. Are you planning on updating these from a form? If so, this sample will work for you.

Ok, let's look at this logically. First of all, only the available computers need to be shown. So, we're going to add an employee id to the computers table as the foreign key. So for the computers table we have:

Computers
SerialNum - Text - Primary Key
CompDesc - Text - 'Computer Description
EmplIDFK - Text - Foreign Key

The employees table will mainly just be a lookup/demographic table (EmployeeID, EmployeeName)

Employees
EmployeeID - Text - Primary Key
Employname - Text

Base a query directly on the computers table, and for the criteria of the EmplIDFK, put this:
Is Null

Now, build a form directly on this query. Add a combo-box using the wizard based on the employeeid and employeename from the employee table, and store the value in EmplIDFK. In the properties for the combo-box, click events, and go to the "After Update" event, right click, build - code, then you will get the vba window. Type this:
Me.ReQuery

That's all there is to it.
(in my sample, I hid the emplidfk, and made the form continuous)
Here is a sample:

Attached Files

computers.zip (13.4 KB, 4 views)

thanks man, finally i made it, with this code!! if someone need something like this

First of all, congrats on getting it sorted. However, there are a few things you should consider:

Domain aggregate functions are SLOW (dlookup, dcount, dfirst, dlast, dsum.....etc etc). If you want a demonstration of this, change your form to continuous form and watch it stutter as it goes through every record squared. say you have 3 records..... well on record 1, it does a dlookup that searches all 3 records for your search. record 2, same thing, and so does record 3. That's 9 records checked for 3 records.

1000 records would give you a million (1,000,000) records checked.

Personally, I would have never displayed a computer that was already taken (as per my sample), therefore, you wouldn't have to run the dlookup to check if it was already taken.

If it were my database, I would have had a return computer form, an issue computer form, and a show all computers form that listed all of the computers and owners whether null or not.

This keeps the database simple. Each action in its own form. Don't have to decode a bunch of vba down the road if you have to change the database.

That's just me though..... I create so many databases, that when I have to go back to one, I don't want to have to sort through all the queries and then the vba..... the queries should do the lionshare of work in your databases. Yes, sometimes it's unavoidable to do a good portion in vba; but if you can do it in a query, it's usually better.

My two cents