If then Statements please help!!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to build an If then Statement into vb. I have two databases.
"W0005.mdb" and "TheBBL1.mdb. I'm building the vbcode in "TheBBL1.mdb."

In the W0005.mdb database I have a table called W0005 with a field named
Recon Approval Date.

In the TheBBL1.mdb database I have a table called Trade Information Table
with a field named Date Booked.

I want to prevent data from being Appended from the W0005.mdb "W0005" table
into a table called "W0005" in "TheBBL1.mdb" if W0005.mdb Recon Approval Date
= TheBBL1.mdb Trade Information Table. Date booked.

Otherwords, Date Booked = Recon Approval Date.

Does anyone know what the code would look like?

Thanks
Don
 
Dates said:
I'm trying to build an If then Statement into vb. I have two databases.
"W0005.mdb" and "TheBBL1.mdb. I'm building the vbcode in "TheBBL1.mdb."

In the W0005.mdb database I have a table called W0005 with a field named
Recon Approval Date.

In the TheBBL1.mdb database I have a table called Trade Information Table
with a field named Date Booked.

I want to prevent data from being Appended from the W0005.mdb "W0005" table
into a table called "W0005" in "TheBBL1.mdb" if W0005.mdb Recon Approval Date
= TheBBL1.mdb Trade Information Table. Date booked.

Otherwords, Date Booked = Recon Approval Date.
Click to expand...


Easiest is to create a linked table in the code mdb to the
W0005 table in the W0005 mdb. Then you can just do a
DLookup to see if the date is in the linked table.

If for some reason you don't want to create a linked table,
you can still create a query to the table in the W0005 mdb
by using the SourceDatabase property. Then you can use
DLookup on that query.
 
Marsh,

Thanks for you response. I was wondering also, if it is possible to Append
Data over to another database just once a day. I have a command button built
onto a form. Within that command button, I have several queries within that
command. Is it possible to click that button once and then have the button
"darken?" So that I can't append data again for the current day?

Thanks
Don
 
Dates said:
Thanks for you response. I was wondering also, if it is possible to Append
Data over to another database just once a day. I have a command button built
onto a form. Within that command button, I have several queries within that
command. Is it possible to click that button once and then have the button
"darken?" So that I can't append data again for the current day?
Click to expand...


Sure you can do that, but you have to have a place to save
the date of the last time the button code executed.

Let's say you have a simple one row table with a date field
for this purpose. Then the code in the Form's Load event
would be something like this air code:

Me.nameofbutton.Enabled = _
(Nz(DLookup("lastdate", "onerowtable "), 0) < Date)

And the code in the button would be something like:

Dim strSQL As String
'do the work
. . .
strSQL = "UPDATE onerowtable " _
& "SET lastdate=" & Format(Date, "\#m\/d\/yyyy\#")
CurrentDb.Execute strSQL, dbFailOnError
Me.nameofbutton.Enabled = False
 
Marsh,

Thanks for your Response. I'm still having some difficulty with the code.
Here is the current code that I have.

Private Sub Command117_Click()
On Error GoTo Err_Command117_Click
DoCmd.SetWarnings False
Dim intResponse As Integer
Dim strPrompt As String

strPrompt = "You are about to Append data from the CancelResetW0005.mde!"

intResponse = MsgBox(strPrompt, vbYesNo, "Broker Billed Losses")

If intResponse = vbYes Then
Dim stDocName As String

stDocName = "Updates Calculation in CancelResetW0005"
DoCmd.OpenQuery stDocName, acNormal, acEdit

stDocName = "Appends to W0005 Daily"
DoCmd.OpenQuery stDocName, acNormal, acEdit

stDocName = "Update Type"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command117_Click:
Exit Sub

Err_Command117_Click:
MsgBox Err.Description
Resume Exit_Command117_Click
End If
End Sub

I have created a table called LastW0005 with a field called LastDate. I
guess I'm a little confused on where I put the Me.nameofbutton.Enabled =
(Nz(Dllokup("Lastdate", "onerowtable"), 0) <Date)? Where does this go?

Thanks
Don
 
Dates said:
Thanks for your Response. I'm still having some difficulty with the code.
Here is the current code that I have.

Private Sub Command117_Click()
On Error GoTo Err_Command117_Click
DoCmd.SetWarnings False
Dim intResponse As Integer
Dim strPrompt As String

strPrompt = "You are about to Append data from the CancelResetW0005.mde!"

intResponse = MsgBox(strPrompt, vbYesNo, "Broker Billed Losses")

If intResponse = vbYes Then
Dim stDocName As String

stDocName = "Updates Calculation in CancelResetW0005"
DoCmd.OpenQuery stDocName, acNormal, acEdit

stDocName = "Appends to W0005 Daily"
DoCmd.OpenQuery stDocName, acNormal, acEdit

stDocName = "Update Type"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command117_Click:
Exit Sub

Err_Command117_Click:
MsgBox Err.Description
Resume Exit_Command117_Click
End If
End Sub

I have created a table called LastW0005 with a field called LastDate. I
guess I'm a little confused on where I put the Me.nameofbutton.Enabled =
(Nz(Dllokup("Lastdate", "onerowtable"), 0) <Date)? Where does this go?
Click to expand...


As I tried to say before, that line goes in the form's Load
event procedure.

The other lines I posted go in your button's code right
after the last OpenQuery.
 
Marshall,

I got the Dim strSQL As String
strSQL = "Update..." to work.

I'm still having a problem with the Me.nameofbutton.Enabled Lookup. I guess
I don't understand what nameofbutton refers to or would refer to? I
understand where the code goes into the Form Load button. That creates a new
line of code in VB.

Private Sub Command117_Click()
DoCmd.SetWarnings False
Dim strSQL As String
Dim intResponse As Integer
Dim strPrompt As String

strPrompt = "You are about to Append data from the CancelResetW0005.mde!"

intResponse = MsgBox(strPrompt, vbYesNo, "Broker Billed Losses")

If intResponse = vbYes Then
Dim stDocName As String

stDocName = "Updates Calculation in CancelResetW0005"
DoCmd.OpenQuery stDocName, acNormal, acEdit

stDocName = "Appends to W0005 Daily"
DoCmd.OpenQuery stDocName, acNormal, acEdit

stDocName = "Update Type"
DoCmd.OpenQuery stDocName, acNormal, acEdit

strSQL = "UPDATE [LastW0005] " & "SET [Date]=" & Format(Date,
"\#m\/d\/yyyy\#")
CurrentDb.Execute strSQL, dbFailOnError
End If
End Sub

I'm trying to stop data from being appended over from another database. In
the nameofbutton.enabled. Do I have to enter the name the append query? I
can't type in Comman117. I feel that I am close on this, but just can't seem
to figure it out.

I appreciate your patience.

Thanks
Don
 
From your code, it looks like the name of your button is
Command117. If that's correct, then the code would be:

Me.Command117.Enabled = _
(Nz(DLookup("[Date]", "LastW0005"), 0) < Date)

You really should not use Date (or any other reserved word)
for a name in your application, it's the name of a built in
function and sooner or later, you and/or Access is going to
be confused about what you mean.
 
Marshall,

I removed the Me.Command117.Enabled in my code. It works great... Thanks
for the help.

Don
 
Back
Top