VBA querying an Access dbase 1

[CIMTEET]

I have two tables in the same database. One holds recipe numbers that do not change (Recipes). The other holds the part information (ZoneData). The zonedata table will be searching the recipe table to see if it holds the right number. I have code right now that doesn't seem to be working consistantly. I am checking every "BDSBatch" field in the zonedata table. Basically just searching if it even exists in the Recipes table. Is there anyway to just get an accurate true/false statement? I have tried .absoluteposition, keep getting a "-1" or "0". Tried .Nomatch and received a false every time. Even after the dbase was altered so there wouldn't be a match. Help. You'll notice that I was trying different things to see if I could capture a match or nomatch.

Do
ConnectString = "SELECT * FROM ZoneData WHERE [ID] = " & RecCount '&" 'TRUE AND [Robot] = 1" '& RobotCount
Set m_ZoneData = m_MasterDatabase.OpenRecordset(ConnectString)

With m_ZoneData
RecipeCheck = 1
RecipeConnectString = "SELECT * FROM Recipes WHERE [Recipe_Name] = " & "'" & .Fields("BDSBatch").Value & "'"

Set m_RecipeData = m_MasterDatabase.OpenRecordset(RecipeConnectString)

RecipeCheck = 0
RecCount = RecCount + 1

If m_RecipeData.NoMatch = False Then
x = True
End If
zz = m_RecipeData.Fields("BDSBatch").Value

End With

MsgBox y, vbOKOnly

Loop Until (RecCount > 24) Or (x = True)

If RecCount = 25 Then
GoTo DataRecordError
End If

 

[PHV]

The NoMatch property should be tested only after a FindXxxx call.
Anyway why not using only one recordset with a JOIN ?
Have a look at the EOF property of the Recordset object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CIMTEET]

Does it make a difference that the VBA is not in access? I am using Rockwell software. Do you have any "join" examples?

 

[PHV]

Why not elaborate on what are ZoneData and Recipes, and what you want to do ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CIMTEET]

Table ZoneData Field BDSBatch has a number in it that needs to be found in Table Recipe Field Recipe_Name. There are seven or 8 records in the Recipe table. These are system recipes and will not change. The zonedata/BDSBatch is a number entered by the operator that downloads this information into the paint system and I need to know if that data exists in the Recipe table.

 

[PHV]

A starting point:
ConnectString = "SELECT Z.ID,Z.BDSBatch,R.Recipe_Name FROM ZoneData Z LEFT JOIN Recipes R ON Z.BDSBatch=R.Recipe_Name"
Set m_ZoneData = m_MasterDatabase.OpenRecordset(ConnectString)
x = True
With m_ZoneData
If .EOF Or .BOF Then
MsgBox "ZoneData is empty"
x = False
End If
While Not .EOF
If IsNull(.Fields("Recipe_Name")) Then
MsgBox "No Recipe_Name for BDSBatch='" & .Fields("BDSBatch") & "', ID=" & .Fields("ID")
x = False
End If
.MoveNext
WEnd
.Close
End With
Set m_ZoneData = Nothing
If Not x Then
GoTo DataRecordError
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CIMTEET]

The r. and the z. i figured were just abbreviations because you didn't want to write it out. What are the Zs and Rs on this line:

FROM ZoneData Z LEFT JOIN Recipes R ON

 

[PHV]

They are named alias in the SQL's jargon.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CIMTEET]

Oh, the Z and R were nicknames you can assign the table, cool and thanks. I haven't found anywhere yet that says this but do the fields have to have the same name?

 

[CIMTEET]

Its really looking like the inner join will be what I want. I am looking for any non-matching fields. Something from the Zonedata.bdsbatch field will always have to be found in the Recipes.Recipe_name field, or downloading should stop and the tags cleared. Thanks for the direction, and your hope is not deferred. You helped lots.

 

[CIMTEET]

What is the ISNULL checking for again? Is that stating that No there was no match in that field?? If thats the case, then what you did was perfect.

 

[PHV]

Your guess is right.
Have a look here:

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CIMTEET]

What if I really need to check one field from BDSBatch against all fields in Recipe_Name for one match?

 

[PHV]

check one field from BDSBatch against all fields in Recipe_Name
???
Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CIMTEET]

PHV, your the man, got it and it works!!!