After updatae event not work!!!

[abzalali]

Dear Expert,
I need to update "Fish Type" and "Fish" if Like in "Aquisition & Lab Cost Calculation" with Forms "Fish Type" and "Fish" with this code below.

Private Sub Form_AfterUpdate()
    Dim db As DAO.Database, sSQL As String
    Set db = CurrentDb()
        
    If DLookup("[Fish Type]", "Aquisition & Lab Cost Calculation", "[Fish Type]= Like*'" & Me.FishType & "'* AND [Fish]= Like *'" & Me.Fish & "'*") > 0 Then
    
    ' "UpdateFishType and Fish to Aquisition & Lab Cost Calculation"
    sSQL = "UPDATE " _
                    & "[Aquisition & Lab Cost Calculation] " _
                    & "INNER JOIN [Fish Price] ON [Aquisition & Lab Cost Calculation].Fish = [Fish Price].Fish " _
                    & "SET " _
                    & "[Fish Price].[Fish] = [Aquisition & Lab Cost Calculation]![Fish];"
    db.Execute sSQL, dbSeeChanges
    End If

End Sub

Please anybody help me!!

Thanks in Advance
Mir

 

[GinaWhipp]

Hmm, what doesn't work? What happens when you run it? Error messages?

Can you edit the table directly?

 

[jdraw]

Mir,

"Not work" doesn't tell the reader much. We only know what you tell us about you, your database and the issue you are facing. "After Update event Not work" just isn't sufficient info to provide assistance.

Did you do a Debug.Print sSQL to see the rendered SQL?

 

[abzalali]

Dear Sir,
When I edit a "Fish" name and tab to next then showing
Run-time error '3075':
Syntax error (missing operator) in query expression '[Fish Type] = Like *'Angel'* AND [Fish] = Like *'Black Angel - Pomacanthus arcuatus (ML)'*'.

Which table I want to update that I can edit directly.

Thanks
Mir

 

[jdraw]

[Fish Type] = Like *'Angel'* AND [Fish] = Like *'Black Angel - Pomacanthus arcuatus (ML)'*'.


You would not use = Like together.
Use = for specific values, use Like for some sort of "stub processing"/similar strings

 

[abzalali]

Sir jdraw,
When I use Like for only "Fish" in this line then also I'm getting same error:

If DLookup("[Fish Type]", "Aquisition & Lab Cost Calculation", "[Fish]= Like *'" & Me.Fish & "'*") > 0 Then

Thanks
Mir

 

[jdraw]

It is the combination of = Like that is the problem.

eg:
I want to see if I have record for a Fish = Me.Fish
or
I want to see if I have record for a Fish Like "*" & Me.Fish & "*"

What are you trying to do in plain English?

 

[abzalali]

Sir jdraw,
If I use Like "*" & Me.Fish & "*" then it shows: Error 13 'type mismatch'

In plain English:
Actually I have form named "frmFishPirce" which record source is "Fish Price" table. I just need to sync (with form after update event) two table "Fish Price" and "Aquisition & Lab Cost Calculation" When I enter new "Fish" or update any existing "Fish" in "frmFishPrice" Form.

Thanks
Mir

 

[jdraw]

?? I'm not following

You have a form frmFishPrice that uses table [Fish Price] as recordsource.
What is the structure of table "Aquisition & Lab Cost Calculation"?
How are these tables related?
Can you post a jpg of your tables and relationships?

 

[abzalali]

Sir,
Sstructure of table "Aquisition & Lab Cost Calculation" so many filed but "Fish Type" and "Fish" filed of course there.

Actually no relation between two tables.

Is there no way without relationship. Or is there any way to update or insert If form field are matches?

Thanks again for great cooperation sir.
Mir

 

[jdraw]

If the tables are not related how does/should your Sync operation work?
What has to be sync'd?

 

[abzalali]

Sir,
Only two filed are have to be sync "Fish Type" or "Fish".
If there is now way to update or insert with DLookup then you please give me a guideline to do that.

Thanks
Mir

 

[MSAccessRookie]

Dear Sir,
When I edit a "Fish" name and tab to next then showing
Run-time error '3075':
Syntax error (missing operator) in query expression '[Fish Type] = Like *'Angel'* AND [Fish] = Like *'Black Angel - Pomacanthus arcuatus (ML)'*'.

Which table I want to update that I can edit directly.

Thanks
Mir

It is possible that the position of your Wildcard markers is creating at least some of the problems.

See what happens if you do the following (refer to the colorful Text above)

1. Change *' to '*
2. Change '* to *'

-- Rookie

 

[abzalali]

Sir Rookie
With this code same thing happens, could you please this code once again?

If DLookup("[Fish Type]", "Aquisition & Lab Cost Calculation", "[Fish Type]= Like*'" & Me.FishType & "'* AND [Fish]= Like *'" & Me.Fish & "*'") > 0 Then

Thanks
Mir

 

[jdraw]

@Mir

If you are going to use Like
please see http://www.techonthenet.com/access/queries/like.php

 

[BigHappyDaddy]

You didn't make the changes request by MSAccessRookie. Should be:

"[Fish Type]= Like [COLOR=green]'*"[/COLOR] & Me.FishType & [COLOR=green]"*'[/COLOR] AND [Fish]= Like [COLOR=green]'*"[/COLOR] & Me.Fish & [COLOR=green]"*'"[/COLOR]

 

[MSAccessRookie]

Thanks BigHappyDaddy, I believe that is what I was trying to get abzalali to try. Now all we need to do is wait to see the results

-- Rookie