Form Search

  • Thread starter Thread starter khashid via AccessMonster.com
  • Start date Start date
K

khashid via AccessMonster.com

Hi all, I have something in mind that I would want to create any help would
be appreciated.

I have two tables "TransactionDB" and "NewCards". Both tables have one same
field "Card_No".
Table "NewCards" have total 3 fields "Card_No" "Owner" & "Date".

Now I want to have a form where:

- I have a textbox(1) and a button next to it for searching cards.
- I have another box with can be label or textbox(2) to show specific word as
result

Now When user enters the 11 digit card no in the textbox and clicks on search
and event procedure will run that will:

* Search for the card no in table "TransactionDB" field "Card_No" if its
found then textbox(2) will show "USED CARD"

If card is not found then :

* Search for the card no in table "NewCard" field "Card_No" if its found
then textbox(2) will show "New CARD" and show the other two fields of table
"New Card". (I want to have these two fields not visible until card is new.

* If the card is not found in both tables then the TextBox(2) will show
result "Invalid Card"
 
khashid said:
Hi all, I have something in mind that I would want to create any help would
be appreciated.

I have two tables "TransactionDB" and "NewCards". Both tables have one same
field "Card_No".
Table "NewCards" have total 3 fields "Card_No" "Owner" & "Date".

Now I want to have a form where:

- I have a textbox(1) and a button next to it for searching cards.
- I have another box with can be label or textbox(2) to show specific word as
result

Now When user enters the 11 digit card no in the textbox and clicks on search
and event procedure will run that will:

* Search for the card no in table "TransactionDB" field "Card_No" if its
found then textbox(2) will show "USED CARD"

If card is not found then :

* Search for the card no in table "NewCard" field "Card_No" if its found
then textbox(2) will show "New CARD" and show the other two fields of table
"New Card". (I want to have these two fields not visible until card is new.

* If the card is not found in both tables then the TextBox(2) will show
result "Invalid Card"
Click to expand...


You can use this kind of logic:

Dim db As Database
Dim rs As Recordset
Dim strSQL As String

If DCount("*", "TransactionDB", "Card_No=" & textbox1) = 0 _
Then
textbox2 = "USED CARD"
Else
strSQL = "SELECT Owner, Date FROM NewCard & _
& " WHERE Card_No=" & textbox1
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
textbox2 = "New CARD"
textboxA = rs!Owner
textboxB = rs!Date
Else
textbox2 = "Invalid CARD"
End If
rs.Close : Set rs = Nothing
Set db = Nothing
End If
 
Am getting error on this line can u plz make sure

If DCount("*", "TransactionDB", "Card_No=" & textbox1) = 0 _

Marshall said:
Hi all, I have something in mind that I would want to create any help would
be appreciated.
Click to expand...
[quoted text clipped - 23 lines]
* If the card is not found in both tables then the TextBox(2) will show
result "Invalid Card"
Click to expand...

You can use this kind of logic:

Dim db As Database
Dim rs As Recordset
Dim strSQL As String

If DCount("*", "TransactionDB", "Card_No=" & textbox1) = 0 _
Then
textbox2 = "USED CARD"
Else
strSQL = "SELECT Owner, Date FROM NewCard & _
& " WHERE Card_No=" & textbox1
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
textbox2 = "New CARD"
textboxA = rs!Owner
textboxB = rs!Date
Else
textbox2 = "Invalid CARD"
End If
rs.Close : Set rs = Nothing
Set db = Nothing
End If
Click to expand...
 
khashid said:
Am getting error on this line can u plz make sure

If DCount("*", "TransactionDB", "Card_No=" & textbox1) = 0 _
Click to expand...


Which error are you getting?

Double check each name to make sure they are the ones you
actually have in you form and table.

Also check the data type of the Card_No field. If it is a
Text field, thee line should be"

If DCount("*", "TransactionDB", "Card_No=""" & textbox1 &
"""") = 0 _
 
this is exactly what i have
Private Sub Command2_Click()
Dim db As Database
Dim rs As Recordset
Dim strSQL As String


If DCount("*", "[Master Database]", "[Card #]=""" & Search & """") = 0 _

Then
Result = "Renewal Transaction"
Else
strSQL = "SELECT Dealer, Issue_Date FROM [Smart Card] &" _
& " WHERE [Card #]=" & Search
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
Result = "New CARD"
Dealer = rs!Dealer
IssueDate = Issue_Date
Else
Result = "Invalid Card"
End If
rs.Close: Set rs = Nothing
Set db = Nothing
End If
End Sub
 
Which error are you getting?

Remove the blank line between the If and Then lines.
--
Marsh
MVP [MS Access]

this is exactly what i have
Private Sub Command2_Click()
Dim db As Database
Dim rs As Recordset
Dim strSQL As String


If DCount("*", "[Master Database]", "[Card #]=""" & Search & """") = 0 _

Then
Result = "Renewal Transaction"
Else
strSQL = "SELECT Dealer, Issue_Date FROM [Smart Card] &" _
& " WHERE [Card #]=" & Search
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
Result = "New CARD"
Dealer = rs!Dealer
IssueDate = Issue_Date
Else
Result = "Invalid Card"
End If
rs.Close: Set rs = Nothing
Set db = Nothing
End If
End Sub


Marshall said:
Which error are you getting?

Double check each name to make sure they are the ones you
actually have in you form and table.

Also check the data type of the Card_No field. If it is a
Text field, thee line should be"

If DCount("*", "TransactionDB", "Card_No=""" & textbox1 &
"""") = 0 _
Click to expand...
Click to expand...
 
Instead of Dcount can i use something for search? now am getting error in
line

Set rs = db.OpenRecordset(strSQL)

Marshall said:
Which error are you getting?

Remove the blank line between the If and Then lines.
this is exactly what i have
Private Sub Command2_Click()
Click to expand...
[quoted text clipped - 37 lines]
Click to expand...
 
I really need to make it work, is it possible if i have textbox and user
enters the no to search, it would search first table give results then search
second table and give results?
khashid said:
Instead of Dcount can i use something for search? now am getting error in
line

Set rs = db.OpenRecordset(strSQL)
Which error are you getting?
Click to expand...
[quoted text clipped - 4 lines]
Click to expand...
 
WHICH ERROR ARE YOU GETTING???

Also post Copy/Paste of the entire procedure as you have it.

Then provide the name of each table field and it's datatype.
Include the values in each control at the time of the error.

There are several alternatives to using DCount, but they are
all more complex. If you can't get a simple DCount working,
I seriously hesitate to take off in another direction.

You should compile the code (Debug menu) before you try to
run it.
--
Marsh
MVP [MS Access]

Instead of Dcount can i use something for search? now am getting error in
line

Set rs = db.OpenRecordset(strSQL)

Marshall said:
Which error are you getting?

Remove the blank line between the If and Then lines.
this is exactly what i have
Private Sub Command2_Click()
Click to expand...
[quoted text clipped - 37 lines]
If DCount("*", "TransactionDB", "Card_No=""" & textbox1 &
"""") = 0 _
Click to expand...
Click to expand...
Click to expand...
 
Back
Top