IF Then Help please and more!!! 1

[DarkOne72]

Ok, I am need of help since I had to ditch a recent wanna be solution.
I have 2 tables Main and Tech_Directory...What I need to happen is on the form once the data is filled out and hit the submit button I need it to check and see if [Tech_Number] on the form (which is pulled/stored on the main table) is listed in the Tech_Direcotry table; if it is then just run the update query I made. If it isn't listed there then run an append query to add certain fields to the tech_directory table saving the information of course.
Once that happens (hopefully in the same click event) I need it to export out to an excel sheet to a shared drive \\Myshare\App_Date
Any help would GREATLY be Apreciated!

Thank you

 

[PHV]

I'd use the DLookUp function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DarkOne72]

I haven't worked with dlookups... not sure how to properly execute what i need to be done.

Sorry

 

[MajP]

http://www.techonthenet.com/access/functions/domain/dlookup.php

 

[DarkOne72]

I cannot seem to get this to work the way I need it too.
Is there a way to have a code that looks at 1 field, Tech_number, and checks against another table and if it is in there run this query (phone_update); if it isn't in there then run an append query (phone_append) ?

RunCommand acCmdSaveRecord
If [Tech_Number] = [Tech_Direcotry]![Tech_Num] Then
DoCmd.OpenQuery "Phone_Update"
ElseIf [Tech_Number] <> [Tech_Directory]![Tech_Num] Then
DoCmd.OpenQuery "Phone_Append"
Else
End If
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Tech_Directory", "\\MyShare\App_Data\Tech_Test.xls", False

End Sub

Thanks.

 

[PHV]

Did you some search about DLookUp ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DarkOne72]

Yes, I can't seem to get it to work every way I have tried I get an S with a dot underneath..don't even know where that cames from or what it means.

 

[dhookom]

every way I have tried

Can you share your best attempts at the code?

Duane
Hook'D on Access
MS Access MVP

 

[DarkOne72]

This is one of them but have tried many ways with the fields

=DLookUp("[Tech_Number]","[Tech_Directory]","[Tech_Num]='" & [Tech_Number] & "'")

The field that is on the form is Tech_Number and I need what ever value is in there to look up against another table Tech_Directory and see if it matches the Tech_num column. If it does then i need it to run the phone_update query, if not the run the phone_append qeury.

I know this probably isn't that hard for you guys but it is killing me and this is the last thing I am needing to work and quite quickly. I have been stuck on this for 2 days browising the net and asking for help can anyone assist me in getting this working instead of just links please?

 

[PHV]

Something like this ?

If Me!Tech_Number = DLookUp("Tech_Number","Tech_Directory","Tech_Num='" & Me!Tech_Number & "'") Then
  DoCmd.OpenQuery "Phone_Update"
Else
  DoCmd.OpenQuery "Phone_Append"
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

OOps, saw the error:

If Me!Tech_Number = DLookUp("[!]Tech_Num[/!]","Tech_Directory","Tech_Num='" & Me!Tech_Number & "'") Then
  DoCmd.OpenQuery "Phone_Update"
Else
  DoCmd.OpenQuery "Phone_Append"
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DarkOne72]

Awesome! I believe we are on the right track but I get and error of "Data Type Mismatch in criteria expression"

Thanks you again!

 

[dhookom]

Apparently something in your expression is a number and something is a string/text. Did you read this section of the link provided previously?

You could also use a form control in the DLookup function. For example
[tt]
DLookup("CustomerID", "Orders", "OrderID = " & Forms![Orders]!OrderID)
[/tt]

Duane
Hook'D on Access
MS Access MVP

 

[DarkOne72]

Weird, I made the correction and I still get the same error?

 

[PHV]

"Data Type Mismatch in criteria expression"

If Me!Tech_Number = DLookUp("Tech_Num","Tech_Directory","Tech_Num=" & Me!Tech_Number) Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DarkOne72]

Awesome...That did the trick..thank you so much for your patience and understanding! I really appreciate it.