Checking for data with the same number in another table

[Burden]

Ok this is driving me mad. I can never get this right.

I have two tables, both with a field called SON. One table contians
the main data and customers and job status. The other table is for
logging faults and failures of that job.

On my main form i have created a button called "Log Fault" that
creates a fault log. This stores the data into the [Fault Logging]
table. I also have a button called "Close Fault" which opens a form
which is getting it's data from a query that runs that shows only the
records that match the main forms SON number.

Query Criteria
Loaded forms
[forms]![form - touch find]![son]

This works great!

Now this is where i am stuck. I have made the "Close Fault" button
disabled and would like it to become enabled when i am on the record
on the main form [form - touch find] if the [SON] number exist's in
the [Fault Logging] table.

Does anyone have any ideas.

I have done this before but it was looking at a field in the same
table. I only have problems when i need to look at another table to
get my result.

 

[Allen Browne]

Use DLookup() in the Current event of the form, to see if there is a
matching record in the related table.

If you need help with DLookup, see:
http://allenbrowne.com/casu-07.html

 

[Ryan]

You need to base the touch find main form on a query, and in that query you
need to add the table for fault logging. If you dont already have a
realtionship for the two tables, you need to make on in the query. Now that
both table are in the query, add all the fields from touch find, and the son
field from fault logging. On your form add the son field, and set its
visible properties to false if you dont want to see it. Then add this code
to the On Current event.

Private Sub Form_Current()
If Not IsNull (Me!Close_Fault) Then
Me.Close_Fault.Enabled = True
Else
Me.Close_Fault.Enabled = False
End If
End Sub

 

[Ryan]

Ooops, messed up a little on the code. It should be

If Not IsNull (Me!Son) Then
Me.Close_Fault.Enabled = True
Else
Me.Close_Fault.Enabled = False
End If
End Sub
--
Please remember to mark this post as answered if this solves your problem.

You need to base the touch find main form on a query, and in that query you
need to add the table for fault logging. If you dont already have a
realtionship for the two tables, you need to make on in the query. Now that
both table are in the query, add all the fields from touch find, and the son
field from fault logging. On your form add the son field, and set its
visible properties to false if you dont want to see it. Then add this code
to the On Current event.

Private Sub Form_Current()
If Not IsNull (Me!Close_Fault) Then
Me.Close_Fault.Enabled = True
Else
Me.Close_Fault.Enabled = False
End If
End Sub
--
Please remember to mark this post as answered if this solves your problem.

Ok this is driving me mad. I can never get this right.

I have two tables, both with a field called SON. One table contians
the main data and customers and job status. The other table is for
logging faults and failures of that job.

On my main form i have created a button called "Log Fault" that
creates a fault log. This stores the data into the [Fault Logging]
table. I also have a button called "Close Fault" which opens a form
which is getting it's data from a query that runs that shows only the
records that match the main forms SON number.

Query Criteria
Loaded forms
[forms]![form - touch find]![son]

This works great!

Now this is where i am stuck. I have made the "Close Fault" button
disabled and would like it to become enabled when i am on the record
on the main form [form - touch find] if the [SON] number exist's in
the [Fault Logging] table.

Does anyone have any ideas.

I have done this before but it was looking at a field in the same
table. I only have problems when i need to look at another table to
get my result.

 

[Burden]

Ooops, messed up a little on the code.  It should be

If Not IsNull (Me!Son) Then
Me.Close_Fault.Enabled = True
Else
Me.Close_Fault.Enabled = False
End If
End Sub
--
Please remember to mark this post as answered if this solves your problem..

You need to base the touch find main form on a query, and in that queryyou
need to add the table for fault logging.  If you dont already have a
realtionship for the two tables, you need to make on in the query.  Now that
both table are in the query, add all the fields from touch find, and the son
field from fault logging.  On your form add the son field, and set its
visible properties to false if you dont want to see it.  Then add this code
to the On Current event.

Private Sub Form_Current()
If Not IsNull (Me!Close_Fault) Then
Me.Close_Fault.Enabled = True
Else
Me.Close_Fault.Enabled = False
End If
End Sub

Ok this is driving me mad. I can never get this right.
I have two tables, both with a field called SON. One table contians
the main data and customers and job status. The other table is for
logging faults and failures of that job.
On my main form i have created a button called "Log Fault" that
creates a fault log. This stores the data into the [Fault Logging]
table. I also have a button called "Close Fault" which opens a form
which is getting it's data from a query that runs that shows only the
records that match the main forms SON number.
Query Criteria
Loaded forms
[forms]![form - touch find]![son]
This works great!
Now this is where i am stuck. I have made the "Close Fault" button
disabled and would like it to become enabled when i am on the record
on the main form [form - touch find] if the [SON] number exist's in
the [Fault Logging] table.
Does anyone have any ideas.
I have done this before but it was looking at a field in the same
table. I only have problems when i need to look at another table to
get my result.- Hide quoted text -

- Show quoted text -

Ok i cant make the main form run from a query as it is getting it's
data from a linked table. The other table is local. The data is is in
both of them and all i want to do is add a bit of code that looks in
the table of the faults and if the same SON is in there as the record
that i am currently on then make the button enabled.

 

[Ryan]

As Allen suggested, DLookup is a great solution. However, you can use a
query even if the table is a linked table. You can use a query on any table,
linked or not.
--
Please remember to mark this post as answered if this solves your problem.

Ooops, messed up a little on the code. It should be

If Not IsNull (Me!Son) Then
Me.Close_Fault.Enabled = True
Else
Me.Close_Fault.Enabled = False
End If
End Sub
--
Please remember to mark this post as answered if this solves your problem..

You need to base the touch find main form on a query, and in that query you
need to add the table for fault logging. If you dont already have a
realtionship for the two tables, you need to make on in the query. Now that
both table are in the query, add all the fields from touch find, and the son
field from fault logging. On your form add the son field, and set its
visible properties to false if you dont want to see it. Then add this code
to the On Current event.

Private Sub Form_Current()
If Not IsNull (Me!Close_Fault) Then
Me.Close_Fault.Enabled = True
Else
Me.Close_Fault.Enabled = False
End If
End Sub

"Burden" wrote:

Ok this is driving me mad. I can never get this right.

I have two tables, both with a field called SON. One table contians
the main data and customers and job status. The other table is for
logging faults and failures of that job.

On my main form i have created a button called "Log Fault" that
creates a fault log. This stores the data into the [Fault Logging]
table. I also have a button called "Close Fault" which opens a form
which is getting it's data from a query that runs that shows only the
records that match the main forms SON number.

Query Criteria
Loaded forms
[forms]![form - touch find]![son]

This works great!

Now this is where i am stuck. I have made the "Close Fault" button
disabled and would like it to become enabled when i am on the record
on the main form [form - touch find] if the [SON] number exist's in
the [Fault Logging] table.

Does anyone have any ideas.

I have done this before but it was looking at a field in the same
table. I only have problems when i need to look at another table to
get my result.- Hide quoted text -

- Show quoted text -

Ok i cant make the main form run from a query as it is getting it's
data from a linked table. The other table is local. The data is is in
both of them and all i want to do is add a bit of code that looks in
the table of the faults and if the same SON is in there as the record
that i am currently on then make the button enabled.