ich habe hier eine MySQL Datenbank, auf die ich mit Base als Frontend zugreife. Die Verbindung wird mit "mysql native" hergestellt.
in dem Formular das ich gerade bearbeite soll eine bestehende Bestellung bei Lieferung markiert werden und gleichzeitig das Inventar aktualisiert werden.
Hierzu wird folgendes Makro verwendet:
Code: Alles auswählen
Sub ConfirmOrderArrival
Dim oMyForm as object
Dim oControl as Object
Dim Filter as String
Dim oPB as Object
Dim oMRI as object
Dim query01 as String 'update EA in Inventory
Dim query02 as String 'update finished in Order
Dim query03 as String 'update finished in Order
Dim oDataBaseContext as Object
Dim oConnection as Object
Dim oChannel as Object
Dim oChannel1 as Object
Dim oChannel2 as Object
Dim oMyResultSet()
Dim i as Integer
Dim j as Integer
Dim realarrived as Integer
InventoryCheck
oMyForm = StarDesktop.CurrentComponent.Drawpage.Forms.getbyName("spareparts").getbyname("Liste")
oControl = oMyForm.getbyname("FilterBox")
Filter = oControl.Text
query01 = "SELECT "+chr(34)+"Spindle-spare-order"+chr(34)+"."+chr(34)+"orderno"+chr(34)+", "+chr(34)+"orderparts"+chr(34)+"."+chr(34)+"quantity"+chr(34)+", "+chr(34)+"spareparts"+chr(34)+"."+chr(34)+"ea_unit"+chr(34)+", "+chr(34)+"spareparts"+chr(34)+"."+chr(34)+"description (en)"+chr(34)+", "+chr(34)+"spare_inventory"+chr(34)+"."+chr(34)+"ea"+chr(34)+", "+chr(34)+"spareparts"+chr(34)+"."+chr(34)+"artno"+chr(34)+", "+chr(34)+"Spindle-spare-order"+chr(34)+"."+chr(34)+"id"+chr(34)+", "+chr(34)+"spare_inventory"+chr(34)+"."+chr(34)+"inventid"+chr(34)+", "+chr(34)+"orderparts"+chr(34)+"."+chr(34)+"id"+chr(34)+" FROM "+chr(34)+"spareparts"+chr(34)+", "+chr(34)+"spare_inventory"+chr(34)+", "+chr(34)+"orderparts"+chr(34)+", "+chr(34)+"Spindle-spare-order"+chr(34)+" WHERE "+chr(34)+"spareparts"+chr(34)+"."+chr(34)+"etno"+chr(34)+" = "+chr(34)+"spare_inventory"+chr(34)+"."+chr(34)+"spareid"+chr(34)+" AND "+chr(34)+"spareparts"+chr(34)+"."+chr(34)+"etno"+chr(34)+" = "+chr(34)+"orderparts"+chr(34)+"."+chr(34)+"partid"+chr(34)+" AND "+chr(34)+"orderparts"+chr(34)+"."+chr(34)+"id"+chr(34)+" = "+chr(34)+"Spindle-spare-order"+chr(34)+"."+chr(34)+"quantityid"+chr(34)+" AND "+chr(34)+"Spindle-spare-order"+chr(34)+"."+chr(34)+"orderno"+chr(34)+" = '"+Filter+"' AND "+chr(34)+"Spindle-spare-order"+chr(34)+"."+chr(34)+"delivered"+chr(34)+" = False ORDER BY "+chr(34)+"spareparts"+chr(34)+"."+chr(34)+"description (en)"+chr(34)
wait 200
if msgBox ("Confirm Parts Arrival"+chr(13)+"Order No. '"+Filter+"'?", 1 , "Attention!") = 1 then
oDataBaseContext = createUNOService("com.sun.star.sdb.DatabaseContext").getByName("grobkorea")
If Not oDataBaseContext.IsPasswordRequired Then
oConnection = oDataBaseContext.GetConnection("grobkorea","")
Else
InteractionHandler = createUnoService("com.sun.star.sdb.InteractionHandler")
oConnection = oDataBaseContext.ConnectWithCompletion(InteractionHandler)
End If
oChannel = oConnection.createStatement()
oMyResultSet = oChannel.executeQuery(query01)
oMRI = CreateUnoService( "mytools.Mri" )
oMRI.inspect( oMyResultSet )
msgBox "Warten...!"
wait 200
i=0
do
i= i+1
oMyResultSet.next
if msgBox ("Has the ordered no. of "+chr(13)+cStr(oMyResultSet.getint(2,i))+" ea of"+chr(13)+oMyResultSet.getString(6,i)+": "+oMyResultSet.getString(4,i)+chr(13)+"been delivered?",4,oMyResultSet.getString(1,i)) = 6 then
query02 = "UPDATE spare_inventory SET ea = "+CStr(oMyResultSet.getint(5,i)+oMyResultSet.getint(2,i)*oMyResultSet.getint(3,i))+" WHERE spare_inventory.inventid = "+CStr(oMyResultSet.getInt(8,i))
query03 = "UPDATE Spindle-spare-order SET delivered = 'TRUE' WHERE "+"Spindle-spare-order.id = "+CStr(oMyResultSet.getString(7,i))
else
realarrived = CInt(inputBox ("Please enter the no. of actual arrived parts for"+chr(13)+oMyResultSet.getString(6,i)+": "+oMyResultSet.getString(4,i)+"!",oMyResultSet.getString(6,i)+": "+oMyResultSet.getString(4,i)+"!",cStr(oMyResultSet.getint(2,i)))
if realarrived < oMyResultSet.getint(2,i) then
query02 = "UPDATE spare_inventory SET ea = "+CStr(oMyResultSet.getint(5,i)+realarrived*oMyResultSet.getint(3,i))+" WHERE spare_inventory.inventid = "+CStr(oMyResultSet.getInt(8,i))
query03 = "UPDATE orderparts SET quantity = "+CStr(oMyResultSet.getint(2,i)-realarrived)+" WHERE orderparts.id = "+CStr(oMyResultSet.getInt(9,i))
else
query02 = "UPDATE spare_inventory SET ea = "+CStr(oMyResultSet.getint(5,i)+oMyResultSet.getint(2,i)*oMyResultSet.getint(3,i))+" WHERE spare_inventory.inventid = "+CStr(oMyResultSet.getInt(8,i))
query03 = "UPDATE Spindle-spare-order SET delivered = 'TRUE' WHERE Spindle-spare-order.id = "+CStr(oMyResultSet.getString(7,i))
endif
endif
oChannel1 = oConnection.createStatement()
msgBox "4"+chr(13)+query02
oChannel1.executeQuery(query02)
msgBox "5"
oChannel2 = oConnection.createStatement()
msgBox "6"
oChannel2.executeQuery(query03)
msgBox "7"
oMyForm.reload()
msgBox "8"
oControl.refresh()
loop while not oMyResultSet.isLast
endif
End Sub
1. Bei msgBox "4"+chr(13)+query02 (nach klicken von ok)
2. innerhalb des mri-Fensters, wenn ich mir den Befehl anzeigen lassen möchte (Properties--Statement)
Hat jemand eine Idee, woran dasliegen kann? Ich dreh mich nun schon seit einigen Tagen im Kreis
Vielen Dank
Grüße
Chrsitian