Results 1 to 13 of 13
  1. #1
    IA-AC is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    6

    Searching value in other table and giving output if it exists

    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.

    Code:
    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

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    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...

  3. #3
    IA-AC is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    6
    Quote Originally Posted by ssanfu View Post
    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?

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    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.
    Code:
    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.

  5. #5
    IA-AC is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    6

    Talking

    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!

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    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??

  7. #7
    IA-AC is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    6
    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

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    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..

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    How is this???

  10. #10
    IA-AC is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    6
    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?

  11. #11
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    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??

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    IA-AC is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    6
    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!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 10-09-2012, 03:20 PM
  2. How to check if Table already exists
    By riaarora in forum Access
    Replies: 1
    Last Post: 08-12-2012, 09:48 AM
  3. Insert if not exists to another table.
    By ramindya in forum Queries
    Replies: 1
    Last Post: 06-12-2012, 07:16 PM
  4. If table exists then
    By SorenIX in forum Programming
    Replies: 2
    Last Post: 06-26-2011, 08:42 AM
  5. Create Table If Not Exists
    By jp2access in forum Queries
    Replies: 7
    Last Post: 07-14-2009, 12:49 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums