Here's one 4 U

[dogman01]

Say I have a control that only accepts 2 values, one would be "Positive" the other "Negative" text values. Then say I have another table called "Positives". Is it possible to set the control up so that any time a value of "Positive" is selected then a new record is created in the "Positives" table. Here's why. One table collects results from testing samples of milk and one of the tests is for ph content but we want any "positive" results for ph content to be sent to a different table for analysis. Thanks 2 the many for your help!!

 

[dkinley]

There are several ways to do this. One way is that on the AfterUpdate event of the control. Let's say its a combo box so they can't mistype the words and can guarentee the decision logic to work.

If Me.cboComboBoxName = "Positive" Then 
    'create a record in the positive table
End If

Now, the issue of 'how do we copy the record or make a new record entry in another table?'

There are several methods ... Jet, DAO, ADO, SQL ... find one that you either familiar with or don't mind getting familiar with and use it. As an example, I demonstrated using SQL in this thread ...
http://www.access-programmers.co.uk/forums/showthread.php?t=162091

The last issue is, because of the event is on the AfterUpdate event, can it fire twice? This would effectively put two records in the other table if the user first chose positive, then negative, and then positive again. The answer is yes so how can you prevent this?

Again, many resolutions such as looking it up in the other table. One simple way is to have another field in your table, say call it "SentPositive" and it can be a boolean type. Then create this field on your form but set it to invisible. Then in the after update event, modify the code to look something like ...

If Me.cboComboBoxName = "Positive" And Me.chkSentPositive = True Then 
    'create a record in the positive table
    Me.txtSentPositive = True
End If

This will create a flag to let you know that the record was already sent and will not fire again.

Again, many ways to do this ...
-dK

 

[datAdrenaline]

Why can you not store the results in the same table, then use a query to return the "Postive" rows and one to return the "Nagative" rows?

You can then use the respective queries for the ensuing tests ...

 

[CEH]

Yet another opinion... I would first rethink your structure. As I read your post...Milk has a positive or negative value for your PH... An option group would be my choice. Two buttons, "Negative" or "Positive"... Your table holding "Milk" would then be related to a "Analysis" table. Sounds like your making it more complicated then it needs to be.... A simple query can show only "positive" results.

 

[gemma-the-husky]

if you only have postiives/negatives, you should be looking at boolean values (which can only be yes or no)

but i dont quite understand why you need additional tables

its easy enough to store everything in one table, and select only those items matchnig the characteristics you need

ie field1 postiive (or true), and field2 negative (or false)