Searching value in other table and giving output if it exists

[IA-AC]

Hi guys,

I am new to this forum and also to access: I have now a new job where I am working with an already existing database (MS Access 2007).
I want to implement a new improvement, so I don't have to look up a number everytime.

The case is:
-Table 1 [Substance] has columns A [CASNo] and B [SVHCtemp].
-Table 2
[ListofSVHCs] has column C [CASnumber].
-Access had to look up the value of table 1, column A in table 2, column C.
-If the value is found table 1, column B had to become 'yes'. Otherwise it becomes 'no'.



A friend of mine is a programmer, but he knows nothing about access at all.
Together we have come up with the next code, which contains some errors.

Option Compare Database


IF                                 Compile error: Expected: Expression
    Substance.CASNo
IN                                 Compile error: Expected: Line or Lable or Statement of End of Statement
    SELECT ListofSVHCs.SubstanceCASNo Compile error: Expected: Case
    FROM ListofSVHCs
THEN                             Compile error: Expected: Line or Lable or Statement of End of Statement
    Update Substance
    Set Substance.SubstanceSVHCtemp = yes
    WHERE Substance.SubstanceCASNo = ListofSVHCs.SubstanceCASNo
Else
    Update Substance
    Set Substance.SubstanceSVHCtemp = no

I have done this via Visual Basic --> Insert Module, even though I have no clue at all whether that is the right location or not.

Can anyone help me with this problem? Or even steer in me in the right direction?
Help would be very welcome!

Regards,

IA-AC

[ssanfu]

Welcome to the forum

What are the field types for the fields: (string, number, y/n)
[CASNo] -
[SVHCtemp] -
[CASnumber] -

When do you want to update the field [SVHCtemp]?
When a record is saved? After data has changed in a field?

Inquiring minds need to know...

[IA-AC]

Welcome to the forum

What are the field types for the fields: (string, number, y/n)
[CASNo] -
[SVHCtemp] -
[CASnumber] -

When do you want to update the field [SVHCtemp]?
When a record is saved? After data has changed in a field?

Inquiring minds need to know...

Dear Steve, Thanks for the reply.

All the field types are text.
The [SVHCtemp] can also be changed to yes/no, since it is a new field. I do not know what is preferable.

And your question on when I want the field to be updated: preferably automatically after filling in [CASNo], but a click on a button somewhere would also be fine.

Can you do anything with this information?

[ssanfu]

The [SVHCtemp] can also be changed to yes/no, since it is a new field. I do not know what is preferable.

Whatever you want/need the field type to be..


It would really help to see your dB, to see how you have this set up.


So, if you have a control named "CASNo" on a form, add this code to the after update event of the control.

Option Compare Database  'should be the first line of every module
Option Explicit                   'should be the second line of every module

'requires a reference to Microsoft DAO 3.6 Object Library

Private Sub CASNo_AfterUpdate()
   Dim dB As DAO.Database
   Dim rs As DAO.Recordset
   Dim sSQL As String

   Set dB = CurrentDb

   sSQL = "SELECT Substance.CASNo"
   sSQL = sSQL & " FROM Substance INNER JOIN ListofSVHCs ON Substance.CASNo = ListofSVHCs.CASnumber"
   sSQL = sSQL & " WHERE Substance.CASNo = '" & Me.CASNo & "';"

   Set rs = dB.OpenRecordset(sSQL)


   If rs.BOF And rs.EOF Then
      sSQL = "UPDATE Substance SET Substance.SVHCtemp = 'No'"
   Else
      sSQL = "UPDATE Substance SET Substance.SVHCtemp = 'Yes'"
   End If

   sSQL = sSQL & " WHERE Substance.CASNo = '" & Me.CASNo & "';"

   dB.Execute sSQL, dbFailOnError

   rs.Close
   Set rs = Nothing
   Set dB = Nothing

End Sub

This will update Substance.SVHCtemp in the table. If there is a control on the form bound to SVHCtemp, the value might not show up without re-querying the form. It would be better to modify the code to push the value (Yes/No) into the control.

[IA-AC]

Hi en thanks again for the help

I'm sorry to say that I am not allowed to give you our dB, since it is confidential. It would have made things a lot easier though...

I'v made a new form and entered the code exactly as you said. Unfortunately, nothing really happens, also when i run the query form after filling in the CASNo field. I did this both for a y/n and a text field.

Just to be sure: this

'requires a reference to Microsoft DAO 3.6 Object Library'

text I also included in the code.

Let me know your thoughts!

[ssanfu]

Just to be sure: this 'requires a reference to Microsoft DAO 3.6 Object Library'

text I also included in the code.

This means there should be a reference set for the Microsoft DAO 3.6 Object Library, not text in the code.

Set a breakpoint on the line "Set rs = dB.OpenRecordset(sSQL)".
Single step through the code (F8 key).
Does the value appear in the table?

I'v made a new form

OK, let's try something else.

Attached is an example dB, pushing the data into the table.
Is this what you are trying to do??

[IA-AC]

Hi Steve,

Thanks once again.

Regarding the reference. I tried to select the Microsoft DAO 3.6 Object Library in visual basic, but I received the error: Name conficts with existing module, project of object library.
Any thoughts on this?

You made a nice working example on the dB, but I had something else in mind. My intention was to fill the CASno value directly into the table, not via a form. Preferably the yes/no would appear directly after filling in this value. Is this even possible?
If you think this is all too much effort, then I would say we drop the issue and I keep on filling the SVHCtemp manually.

Cheers,

IA

p.s. I work on a parttime basis, so I do not respond every day

[ssanfu]

OK, I'll try and look at it tonight.

My intention was to fill the CASno value directly into the table

That can be done.

Preferably the yes/no would appear directly after filling in this value

Appear where???

Both the CASno value and SVHCtemp value will be in the table, but the only way anyone should be looking at the data is via a form.


BTW, pushing the value into a bound control is effectively the same as pushing the value into a table, although in the form method, the record must be saved to have the value saved to the table..

[ssanfu]

How is this???

[IA-AC]

Hi Steve,

I suppose what you send is working very well. But my initial intention of the issue was to reduce time.

In this case I still have to fill in the value in the new form, which pushes the output back into the table.
Since I do not use the form for all the other data in the table, I enter the data in two different locations.

So now there are two options:
-Try to do the same trick, but now without the form.
-Entering all my data in a form format and incorporating your code.

Since the last option will take too much time on a short term, I still prefer the first option. Is that even possible?
That is: entering the CASNo in the Substance table (not in the form) and then receiving a yes or no?

[ssanfu]

Since the last option will take too much time on a short term, I still prefer the first option. Is that even possible?
That is: entering the CASNo in the Substance table (not in the form) and then receiving a yes or no?

No......... Tables do not have events, so you cannot execute any code.

-Entering all my data in a form format and incorporating your code.

Why can't you use a form? Create the form, set the view to continuous forms, add the field(s), add the code on the form afterupdate event. Looks just like a table; plus you have sort capability (which tables don't have) and you have form/control events.

Users should never have access to tables. There is no validation, no control of what is entered.

-Try to do the same trick, but now without the form.

You could enter all of the data in the table, then go to the immediate window and manually execute some code there to do what you want.

But my initial intention of the issue was to reduce time.

Sorry. I just don't understand....
Why do you think entering data into a form is slower than entering the data directly into a table??

[June7]

My 2 cents: Why save the Yes/No into table? Saving calculated values is usually bad idea. The Yes/No can be calculated whenever needed.

[IA-AC]

Hi guys,

Thanks again on helping.
I do believe you shen you say that entering data in a form is better, but then I have to change a lot in the dB. This will be a long term project then.
In the mean time I will drop the subject.

Cheers!