VBA code doesn't work properly when I split the database

[Desperate]

As subject says, I have a vba code including 2 recordsets getting information from different tables and compare with each other. When I dont split the database it works perfect but when I run the code after splitting, values don't come up correctly. What can be the reason for this and how to fix it?

 

[Rabbie]

We need some more information to go on. Can you post the code that is givig you the problem? Also can you display the data in the tables from your new FE DB?

 

[DCrake]

One main pice of code that does not work on a split database is the

Set primaryKey
.Seek

as opposed to .FindNext
.NoMatch

 

[Desperate]

Here is the code;
Public Sub RunAllocation_Click()

Dim db As DAO.Database
Dim STOCK As DAO.Recordset
Dim WP As DAO.Recordset
Dim VirtualStock As Double

DoCmd.SetWarnings False

Set db = CurrentDb()
Set STOCK = db.OpenRecordset("InventoryTempForAll")
Set WP = db.OpenRecordset("WorkPack")

DoCmd.RunSQL "INSERT INTO InventoryTempForAll(Part_No,Stock_Qty) SELECT InventoryTotalQ.Part_No,Stock_Qty FROM InventoryTotalQ,BomQ WHERE BomQ.Part_No = InventoryTotalQ.Part_No;"

If STOCK.RecordCount = 0 Then
DoCmd.OpenQuery "FillEmptyStatusQ"
DoCmd.Requery
Exit Sub
End If

WP.MoveFirst

Do Until WP.EOF

STOCK.MoveFirst

Do Until STOCK.EOF

If WP!Part_No = STOCK!Part_No Then

If WP!Required <= STOCK!Stock_Qty Then

WP.Edit
WP![Status] = "AVAILABLE"
WP.UPDATE

STOCK.Edit
STOCK!Stock_Qty = STOCK!Stock_Qty - WP!Required
STOCK.UPDATE

Else

WP.Edit
WP![Status] = "NOT AVAILABLE"
WP.UPDATE

End If

End If

STOCK.MoveNext

Loop

WP.MoveNext

Loop

STOCK.Close
WP.Close
Set STOCK = Nothing
Set WP = Nothing
Set db = Nothing

DoCmd.OpenQuery "FillEmptyStatusQ"

DoCmd.Requery

Me.NewAllocation.SetFocus

Me.RunAllocation.Enabled = False

End Sub

About datas in table and query: Recordset of WorkPack take material code from WorkPack and its required quantity and compares it with the stock quantity from a queue InventoryTempForAll

 

[DCrake]

So what part of it is not working?

 

[Desperate]

Shall I try to change it from dao to ado?

 

[DCrake]

Why should you? it does not change anything.

 

[Desperate]

Below part is not working. Instead of "AVAILABLE" it gives "NOT AVAILABLE". But if I don't split it works without any problem

If WP!Part_No = STOCK!Part_No Then

If WP!Required <= STOCK!Stock_Qty Then

WP.Edit
WP![Status] = "AVAILABLE"
WP.UPDATE

STOCK.Edit
STOCK!Stock_Qty = STOCK!Stock_Qty - WP!Required
STOCK.UPDATE

Else

WP.Edit
WP![Status] = "NOT AVAILABLE"
WP.UPDATE

End If

End If

 

[Desperate]

any ideas?

 

[JANR]

Set db = CurrentDb()
Set STOCK = db.OpenRecordset("InventoryTempForAll")
Set WP = db.OpenRecordset("WorkPack")

Is InventoryTempForAll and WorkPack local tables or is it Linked Tables??

If it's linked then you can't use default RecordsetType which is dbOpenTable, but you must specify which recordsetType you want.

For linked tables:

Set db = CurrentDb()
Set STOCK = db.OpenRecordset("InventoryTempForAll[COLOR=black]", [COLOR=red]dbOpenDynaset[/COLOR])[/COLOR]
Set WP = db.OpenRecordset("WorkPack", [COLOR=red]dbOpenDynaset[/COLOR])

That's what I read from Access Help.

JR

 

[DCrake]

Beware of using reserved words! Required is a reserved word.

Also why are you wasting a variable?

Dim DB as DAO.Database

Set DB = CurrentDb()

Set Rs = DB.OpenRecordset

Simply

Set Rs = CurrentDb.Openrecordset

works just as fine

 

[Desperate]

My tables are not linked. Its very strange.When I don't split it works without any problem at all.