Form Search

[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"

 

[Marshall Barton]

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"

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

 

[khashid via AccessMonster.com]

Am getting error on this line can u plz make sure

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

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

[quoted text clipped - 23 lines]

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

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

 

[Marshall Barton]

Am getting error on this line can u plz make sure

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

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 _

 

[khashid via AccessMonster.com]

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 Barton]

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

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 _

 

[Marshall Barton]

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)

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()

[quoted text clipped - 37 lines]

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