By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,694 Members | 1,846 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,694 IT Pros & Developers. It's quick & easy.

Check for Values in ExternalDatabase

100+
P: 194
Hi everybody, I am facing great difficulty with my database. My problem is I made a database named ExternalDatabase and then made another database called EntryDatabase. I am reading the data of ExternalDatabase by using the following code which is working perfectly:

Dim dbExternalDatabase As Database
Dim rsMainTable As Recordset
Const strDBPath = "C:\ExternalDatabase.mdb"
Set dbExternalDatabase = OpenDatabase(strDBPath)
Set rsMainTable = dbExternalDatabase.OpenRecordset("select * FROM tbl_MainTable ", dbOpenSnapshot)

Now I made a form named frm_Check to check some values in some fields of tbl_MainTable of ExternalDatabase.

tbl_MainTable of ExternalDatabase have fileds SNO (data type = string), Relation (data type = string) and isAvailable (data type = string)

frm_Check has two unbound text fields named txtSNO and txtRelation and a command button.

When I click on the command button, I wana check the values entered in frm_Check with the Recordset of ExternalDatabase.

I used this:

Do While Not rsMainTable.EOF

If rsMainTable!isAvailable = "No" And Me!txtSNO = rsMainTable!SNO And Me!txtRelation = rsMainTable!Relation Then
MsgBox "Entry Already Exists. Please Enter Correct Values."
Me!txtSNO = ""
Me!txtRelation = ""
Me!txtSNO.SetFocus
Else

rsMainTable.MoveNext
End If
Loop
rsMainTable.Close
dbExternalDatabase.Close
Set rsMainTable = Nothing
Set dbExternalDatabase = Nothing

The problem is that i wana open a form of EntryDatabase named entryFrom when the if condition in the loop comes false after completing the loop because I wana reach at the End Of File (EOF). How I can do this? Please tell me is there any other method by adopting which I can get my required results. Kindly help me and tell me where I am making a mistake. I will be grateful to you.

Please note that tbl_MainEntryTable has a primary key with data type = long integer (increment)
Kindly also note that I am using Microsoft Access 2002 Version.

Regards
Nov 27 '07 #1
Share this Question
Share on Google+
2 Replies


ADezii
Expert 5K+
P: 8,669
Hi everybody, I am facing great difficulty with my database. My problem is I made a database named ExternalDatabase and then made another database called EntryDatabase. I am reading the data of ExternalDatabase by using the following code which is working perfectly:

Dim dbExternalDatabase As Database
Dim rsMainTable As Recordset
Const strDBPath = "C:\ExternalDatabase.mdb"
Set dbExternalDatabase = OpenDatabase(strDBPath)
Set rsMainTable = dbExternalDatabase.OpenRecordset("select * FROM tbl_MainTable ", dbOpenSnapshot)

Now I made a form named frm_Check to check some values in some fields of tbl_MainTable of ExternalDatabase.

tbl_MainTable of ExternalDatabase have fileds SNO (data type = string), Relation (data type = string) and isAvailable (data type = string)

frm_Check has two unbound text fields named txtSNO and txtRelation and a command button.

When I click on the command button, I wana check the values entered in frm_Check with the Recordset of ExternalDatabase.

I used this:

Do While Not rsMainTable.EOF

If rsMainTable!isAvailable = "No" And Me!txtSNO = rsMainTable!SNO And Me!txtRelation = rsMainTable!Relation Then
MsgBox "Entry Already Exists. Please Enter Correct Values."
Me!txtSNO = ""
Me!txtRelation = ""
Me!txtSNO.SetFocus
Else

rsMainTable.MoveNext
End If
Loop
rsMainTable.Close
dbExternalDatabase.Close
Set rsMainTable = Nothing
Set dbExternalDatabase = Nothing

The problem is that i wana open a form of EntryDatabase named entryFrom when the if condition in the loop comes false after completing the loop because I wana reach at the End Of File (EOF). How I can do this? Please tell me is there any other method by adopting which I can get my required results. Kindly help me and tell me where I am making a mistake. I will be grateful to you.

Please note that tbl_MainEntryTable has a primary key with data type = long integer (increment)
Kindly also note that I am using Microsoft Access 2002 Version.

Regards
I'm sorry, but I am having difficulty understanding your request but I think what you may be referring to is a way to Exit the Do While...Loop structure (Line #7). If this is not what you want, please explain in more detail what your request is.
Expand|Select|Wrap|Line Numbers
  1. Do While Not rsMainTable.EOF
  2.   If rsMainTable!isAvailable = "No" And Me!txtSNO = rsMainTable!SNO And Me!txtRelation = rsMainTable!Relation Then
  3.     MsgBox "Entry Already Exists. Please Enter Correct Values."
  4.       Me!txtSNO = ""
  5.       Me!txtRelation = ""
  6.         Me!txtSNO.SetFocus
  7.           Exit Do
  8.   Else
  9.     rsMainTable.MoveNext
  10.   End If
  11. Loop Until rsMainTable.EOF
  12.  
  13. rsMainTable.Close
  14. dbExternalDatabase.Close
  15.  
  16. Set rsMainTable = Nothing
  17. Set dbExternalDatabase = Nothing
  18. Loop
Nov 27 '07 #2

100+
P: 194
I'm sorry, but I am having difficulty understanding your request but I think what you may be referring to is a way to Exit the Do While...Loop structure (Line #7). If this is not what you want, please explain in more detail what your request is.
Expand|Select|Wrap|Line Numbers
  1. Do While Not rsMainTable.EOF
  2.   If rsMainTable!isAvailable = "No" And Me!txtSNO = rsMainTable!SNO And Me!txtRelation = rsMainTable!Relation Then
  3.     MsgBox "Entry Already Exists. Please Enter Correct Values."
  4.       Me!txtSNO = ""
  5.       Me!txtRelation = ""
  6.         Me!txtSNO.SetFocus
  7.           Exit Do
  8.   Else
  9.     rsMainTable.MoveNext
  10.   End If
  11. Loop Until rsMainTable.EOF
  12.  
  13. rsMainTable.Close
  14. dbExternalDatabase.Close
  15.  
  16. Set rsMainTable = Nothing
  17. Set dbExternalDatabase = Nothing
  18. Loop
Thank you dezi for ur reply. i got my solution's problem with "Exit Do". :)
I am sorry if u can't understand my problem but still u solve it :)

Have nice time.
regards

Mohammad Faisal
Nov 27 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.