Matching text and inserting into a Subform

[Sam Summers]

I have been searching for something like this but cant seem to find it.

I need to be able to type in a number into a textbox that is then checked to match a number that is already held in a table but at a different location.
When the user hits Enter the number is then inserted into a new location. i.e. from one job to another job.

I can use an update query to change locations, its just the matching of the number from the table that i'm not sure about.
The number field is not able to be set as a combobox.

Thanks very much in advance.

 

[adam_fleck]

Try DCount?

Hi Sam,

Supposing that users will type the number you want to check into a textbox called "number_To_Check", and will then click on a button called “cmd_Check_Num”, you could try something like this (you’ll need to correct the code so as to match the names of the objects you’ve set up):

Private Sub cmd_Check_Num_Click()

Dim check_Num, c_match

    'Get hold of the number to be checked
    check_Num = Me.number_To_Check

    'Check it actually is a number
    If Not IsNumeric(check_Num) Then
        Exit Sub
    End If

    'Look for the number in the other table
    c_match = DCount("*", "other_table_name", "field_to_look_in=" & check_Num)

    If c_match > 0 Then
        'Since c_match isn't 0 at least on instance of check_num was found
        'Put code here for what you want to happen when check_num exists
    Else
        'No matches were found.  The number doesn't exist in the other table
        'Put code here for what you want to happen when check_num doesn't exist
    End If

End Sub

There main other way of doing what you want to do, is to open a recordset on your other table and look through it to see if check_num exists.

 

[Sam Summers]

Thanks Adam, I'll try that

 

[Sam Summers]

I tried that but it doesn't work.
I should have explained that my text is not a number but a string (letters and numbers).

I have tried this code int the BeforeUpdate Event, but i keep getting 'run-time error 3001 invalid argument' this occurs at the rst.FindFirst strCriteria lines.

*************************************

Dim dbs As Database, rst As Recordset
Dim strCriteria As String
Dim stDocName As String

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Equipment", dbOpenDynaset)
strCriteria = NumberInput.Text
rst.FindFirst strCriteria
If rst.NoMatch Then
MsgBox "incorrect number", vbOKOnly, "EquiTrac"
Else
stDocName = "TransferQry"
DoCmd.OpenQuery stDocName, acNormal, acEdit
Do Until rst.NoMatch
Debug.Print rst!strCriteria
rst.FindNext strCriteria
Loop
End If
rst.Close
Set dbs = Nothing
Forms!Transfer!TransferLocationSubform.Requery
Me.NumberInput.SetFocus

***************************

any ideas anyone?

Thanks

 

[adam_fleck]

Maybe this'll work

strCriteria = "[field_to_look_in] ='" & Me.numberInput & "'"


The first piece of code would probably be fixed if you changed it to:

c_match = DCount("*", "other_table_name", "field_to_look_in='" & check_Num & "'")

When you want to specify a string as a condition in Access Where clauses or Find criteria, you need to put the string in quotes. Single quotes will do, though, which makes things a bit easier.

An more readable way of doing this is maybe to define a constant

Const sQ = "'"

And then whenever you're constructing a string condition you remember to put in the Sq's too.

strCriteria = "[field_to_look_in] =" & sQ & Me.numberInput & sQ