Put SQL query in VBA?

[Henro]

SELECT [NONDEALgerelateerd Query].Begindatum, [NONDEALgerelateerd
Query].Engineer, [NONDEALgerelateerd Query].AgendaItem, [NONDEALgerelateerd
Query].Omschrijving
FROM [NONDEALgerelateerd Query]
WHERE ((([NONDEALgerelateerd Query].Begindatum)=Date()+3))

Can I use the above SQL statement in VBA?

Something like this?

Private Sub Form_Close()
Dim FirstDate As Date
Dim IntervalType As String
Dim Number As Integer
Dim Msg
Dim Voorwaarde
FirstDate = Date
IntervalType = "d"
Number = 3
Msg = "Er is nog geen concrete planning voor " & DateAdd(IntervalType,
Number, FirstDate)
Plusdate = DateAdd(IntervalType, Number, FirstDate)
Voorwaarde = 'SQL STATEMENT'

With Me.RecordsetClone
If Voorwaarde = Empty Then
MsgBox Msg
.AddNew
.Omschrijving = "Nog geen concrete planning"
.Engineer = Me.Engineer
.Begindatum = Plusdate
MsgBox Msg
.Update
Else: End
End If
End With

End Sub

I need the output of above query to be the value examined in the If...
Then... statement.

Any idea's, suggestions?

TIA Henro

 

[Jay Bukstein]

You need to build the query in code something like this,
these lines need to be added into the VBA Code

Dim rs as ADODB.Recordset
Dim rs as ADODB.Connection
Dim strSQL as String
Dim dSelectDate as Date

dSelectDate = DateAdd("dd", Date() + 3)
strsql = "SELECT Begindatum, Engineer, AgendaItem,
Omschrijving FROM [NONDEALgerelateerd Query]
WHERE Begindatum = '" & dSelectDate & "'"

Set rs = New ADODB.Recordset
Set cn = New ADODB.Connection
cn.Connection "DSN=Database"
cn.Open
rs.Open strSQL
rs.MoveFirst

' Then just loop thought the recordset and do you tests..
To move to the next record rs.MoveNext, To test for end
of recordset rs.EOF (While not rs.EOF) code.

 

[Bogdan Zamfir]

Hi,

In guess in the following statement

Voorwaarde = 'SQL STATEMENT'

With Me.RecordsetClone
If Voorwaarde = Empty Then
MsgBox Msg

you mean if the query doesn't return any row, then execute the IF branch,
right?

In this case, you need something like this:

dim db as database
dim Voorwaarde as DAO.Recordset
......
set db = currentdb
set Voorwaarde = db.Openrecordset("your sql statements goes here")
If Voorwaarde.recordCount =0 Then ' no rows returned by the query
MsgBox Msg
' the rest of your code
.....


HTH,
Bogdan
_______________________
Independent consultant

SELECT [NONDEALgerelateerd Query].Begindatum, [NONDEALgerelateerd
Query].Engineer, [NONDEALgerelateerd Query].AgendaItem, [NONDEALgerelateerd
Query].Omschrijving
FROM [NONDEALgerelateerd Query]
WHERE ((([NONDEALgerelateerd Query].Begindatum)=Date()+3))

Can I use the above SQL statement in VBA?

Something like this?

Private Sub Form_Close()
Dim FirstDate As Date
Dim IntervalType As String
Dim Number As Integer
Dim Msg
Dim Voorwaarde
FirstDate = Date
IntervalType = "d"
Number = 3
Msg = "Er is nog geen concrete planning voor " & DateAdd(IntervalType,
Number, FirstDate)
Plusdate = DateAdd(IntervalType, Number, FirstDate)
Voorwaarde = 'SQL STATEMENT'

With Me.RecordsetClone
If Voorwaarde = Empty Then
MsgBox Msg
.AddNew
.Omschrijving = "Nog geen concrete planning"
.Engineer = Me.Engineer
.Begindatum = Plusdate
MsgBox Msg
.Update
Else: End
End If
End With

End Sub

I need the output of above query to be the value examined in the If...
Then... statement.

Any idea's, suggestions?

TIA Henro

 

[Henro]

That was just it!
I did it as:

Private Sub Form_Close()

Dim MyDate As Date
Dim Msg
Dim Titel
Dim db As Database
Dim Voorwaarde As DAO.Recordset

MyDate = DateAdd("d", 3, Date)
Msg = "Er is over drie dagen nog geen concrete planning voor u. Er is
een algemene melding gemaakt voor " & DateAdd("d", 3, Date)
Titel = NaamEngineer() & " heeft nog geen planning op " & MyDate

Set db = CurrentDb
Set Voorwaarde = db.OpenRecordset("SELECT NONDEALgerelateerd.Begindatum,
NONDEALgerelateerd.Engineer, NONDEALgerelateerd.AgendaItem,
NONDEALgerelateerd.Omschrijving FROM NONDEALgerelateerd WHERE
(((NONDEALgerelateerd.Begindatum)=Date()+3) AND
((NONDEALgerelateerd.Engineer)=naamengineer()));")

If Voorwaarde.RecordCount = 0 Then
With Me.RecordsetClone
.AddNew
!Omschrijving = "Nog geen concrete planning"
!Engineer = NaamEngineer()
!Begindatum = MyDate
.Update
MsgBox Msg, vbOKOnly, Titel
End With
Else: End

End If

End Sub

But now how to do it for all engineers?

Hi,

In guess in the following statement

Voorwaarde = 'SQL STATEMENT'

With Me.RecordsetClone
If Voorwaarde = Empty Then
MsgBox Msg

you mean if the query doesn't return any row, then execute the IF branch,
right?

In this case, you need something like this:

dim db as database
dim Voorwaarde as DAO.Recordset
.....
set db = currentdb
set Voorwaarde = db.Openrecordset("your sql statements goes here")
If Voorwaarde.recordCount =0 Then ' no rows returned by the query
MsgBox Msg
' the rest of your code
.....


HTH,
Bogdan
_______________________
Independent consultant

SELECT [NONDEALgerelateerd Query].Begindatum, [NONDEALgerelateerd
Query].Engineer, [NONDEALgerelateerd Query].AgendaItem, [NONDEALgerelateerd
Query].Omschrijving
FROM [NONDEALgerelateerd Query]
WHERE ((([NONDEALgerelateerd Query].Begindatum)=Date()+3))

Can I use the above SQL statement in VBA?

Something like this?

Private Sub Form_Close()
Dim FirstDate As Date
Dim IntervalType As String
Dim Number As Integer
Dim Msg
Dim Voorwaarde
FirstDate = Date
IntervalType = "d"
Number = 3
Msg = "Er is nog geen concrete planning voor " & DateAdd(IntervalType,
Number, FirstDate)
Plusdate = DateAdd(IntervalType, Number, FirstDate)
Voorwaarde = 'SQL STATEMENT'

With Me.RecordsetClone
If Voorwaarde = Empty Then
MsgBox Msg
.AddNew
.Omschrijving = "Nog geen concrete planning"
.Engineer = Me.Engineer
.Begindatum = Plusdate
MsgBox Msg
.Update
Else: End
End If
End With

End Sub

I need the output of above query to be the value examined in the If...
Then... statement.

Any idea's, suggestions?

TIA Henro