Search a table / field if a value is present

[metalheadstorm]

God this is annoying me, im sorry if this has been asked before but ive looked though the forum and the net but my searches havent come up woth anything.... mainly due to that i dont really know what to search for.

So here we go, i have a table in access called hopper that hold some info about a feed hopper ( max volume , feed_ID etc) i also have a table called feed which holds "feed_ID , Catagory" now i want to be able to add a new hopper to the hopper table, i have done that and it works fine but the part im stuck on is i want to check if the Feed_ID entered when creating the hopper is contained in the Feed table. ive been trying to use if and if not statements but im getting all mixed up :D


i hope someone can help :D and i think its kinda simple to do, but as i said i dont know what to search for :(

[QVeen72]

Hi,

Open a Recordset to check ID is present or not :
use this SQL

sSQl = "Select * from MyTable Where ID =" & variablename

if u get RS.eof then (Not Present)

Regards
Veena

[metalheadstorm]

ty for your speedy responce, i had a go at the but being pritty much a noob @ vb :D

this i what i came up with:


form load

Set db3 = OpenDatabase(App.Path + "/cjmillers.mdb")
Set rs3 = db3.OpenRecordset("Feed")


'----------------------


Private Sub Command28_Click()
If txthopper(0).Text = "" Then
MsgBox "Please enter a Hopper ID", vbInformation, "MMS"
txthopper(0).SetFocus
Else
If txthopper(1).Text = "" Then
MsgBox "Please enter a Hopper Number", vbInformation, "MMS"
txthopper(1).SetFocus
Else
If txthopper(2).Text = "" Then
MsgBox "Please enter a Feed ID", vbInformation, "MMS"
txthopper(2).SetFocus
Else
If txthopper(3).Text = "" Then
MsgBox "Please enter a Catagory", vbInformation, "MMS"
txthopper(3).SetFocus
Else
If txthopper(4).Text = "" Then
MsgBox "Please enter a Maximum Feed", vbInformation, "MMS"
txthopper(4).SetFocus
Else
If txthopper(5).Text = "" Then
MsgBox "please enter The Feed Left", vbInformation, "MMS"
txthopper(5).SetFocus
Else

rs2.AddNew

txthopper(0).Text = rs2.Fields(0)
txthopper(1).Text = rs2.Fields(1)
txthopper(2).Text = rs2.Fields(2)
txthopper(3).Text = rs2.Fields(3)
txthopper(4).Text = rs2.Fields(4)
txthopper(5).Text = rs2.Fields(5)

rs2.Update

'-----------------------
Dim sSQL As String

sSQL = "Select * from Feed =" & Feed_ID


rs3.RecordSource = sSQL
rs3.Refresh
rs3.Recordset.MoveFirst
Do Until rs3.Recordset.EOF
rs3.MoveNext
Loop
End If
End If
End If
End If
End If
End If
End Sub


im now getting an error "94"

from txthopper(0).Text = rs2.Fields(0)

im not sure if the code for

Dim sSQL As String

sSQL = "Select * from Feed =" & Feed_ID


rs3.RecordSource = sSQL
rs3.Refresh
rs3.Recordset.MoveFirst
Do Until rs3.Recordset.EOF
rs3.MoveNext


is correct, plz comment :D

thanks once again.

[QVeen72]

Hi,

it shud be:
replace Table_name with ur table name
and Var_Feed_ID with ur variable name


sSQL = "Select * from Table_name Where Feed_ID =" & Var_Feed_ID
set rs3 = db3.Openrecordset(sSQL,dbOpenDynaset)
If rs3.RecordCount =0 Then
'ADD new Code here
Else
'Msg Box for duplicate entry....
End If



Regards
Veena

[metalheadstorm]

ok this is what i got

Private Sub Form_Load()
 
Set db = OpenDatabase(App.Path + "/cjmillers.mdb")
Set rs = db.OpenRecordset("Orders")
 
Set db1 = OpenDatabase(App.Path + "/cjmillers.mdb")
Set rs1 = db1.OpenRecordset("Orders")
 
Set db2 = OpenDatabase(App.Path + "/cjmillers.mdb")
Set rs2 = db2.OpenRecordset("Hopper")
 
Set db3 = OpenDatabase(App.Path + "/cjmillers.mdb")
Set rs3 = db3.OpenRecordset("Feed")

Private Sub Command28_Click()
 
SQL = "Select * from Feed Where Feed_ID =" & txthopper(2).Text
 
 
Set rs3 = db3.OpenRecordset(sSQL, dbOpenDynaset)
If rs3.RecordCount = 0 Then
rs2.AddNew
    txthopper(0).Text = rs2.Fields(0)
    txthopper(1).Text = rs2.Fields(1)
    txthopper(2).Text = rs2.Fields(2)
    txthopper(3).Text = rs2.Fields(3)
    txthopper(4).Text = rs2.Fields(4)
    txthopper(5).Text = rs2.Fields(5)
rs2.Update
 
Else
MsgBox ("Feed ID entered does not exsisist")
End If

i get a error on line
Set rs3 = db3.OpenRecordset(sSQL, dbOpenDynaset)

i think its just that im tired :D all this text is just a blur tbh :D

[metalheadstorm]

Can anyone help ?? :(

[QVeen72]

Hi,

sSQL = "Select * from Feed Where Feed_ID =" & txthopper(2).Text

Check field name :
Feed_ID

txthopper(2) -- value of this text box..

Regards
Veena

[metalheadstorm]

nope same prob
all i get is

run-time error 424
Object required

then under the yellow error text:

dbOpenDynaset =2
sSQL = "Select * from Feed Where Feed_ID =10008"