Input Form to check table and return user info

[Sandworm]

Hi all. This is my first time in the forum and hoping I can get some assitance. I've wrote loads of vb before in Excel (mostly with the assistance of the macro writer) and adapted the code from that point. So Im pretty familiar with If....Else statements, MsgBox's and other bits however Access is a different proposition!
Im trying to construct a relatively simple database, 1 table (tbl y) with 1 input form (frm z). The table carries all the details (Description, Location, Time In, Time Out etc ) of items which are temporarily stored, each item carrying a unique ref no, x. However the same item can exit and be stored again and again and will have the same number, however it will carry a different alphabetical suffix each time it is re-stored i.e. xA,xB,xC etc
I wanted to use 3 or 4 command buttons to cary out certain actions. I am having trouble both decideing weather to use vb (which I think is the right way) or a combo of queries and macros and vb to achieve the same process.
OK Things Im trying to acieve with Command Button 1:
I want the user to enter a number into the URN field of frm z and hit a command button to check the tbl y URN field to see if the items record already exists. If it does exist I want the db to offer to either display the record for edit (YES) or alphabetically suffix the number as a new record and enter the items data already held in the database (NO). Ive prepped a MsgBox with the Yes,No,Cancel options which works fine(on its own admittedly). However I cant seem to find the right line of code to action what I want to do for the Yes or No options. Ive tried

=DLookup("fieldURN", "tbl y", "frm z.fieldURN = '"&[fieldURN]&"'")

for the field as suggested in several help manuals but cant seem to get either the syntax right or it doesnt like the way ive compiled it. Im not even sure DLookup is the right option. If I could get it to work Im unsure of what code I would need to insert the result into the form or how to compile the alternative code to add a suffix tothe number. Alternatively ive tried macros linked to queries (as I need the last record only from the query set) but I cant get a query to run with a query field linked to a form input field which then needs a wild card character to get all the results. (I dont really want the user to see any query searching whilst using it particularly either).
Command Button 2 does a similar search but of the items location to check another item is not already where it is intended to place the current record. Which I presume requires similar "look up" coding. However I would like to include a name field of the item in the MsgBox. Would this be simply using the MsgBox's text parameter such as "The item [itemname] is already in [bay_a] [box_t]" - somehow I think not!
I have SAVE - CLEAR - CANCEL as other Command Buttons but am hopeful
they will work as below

Private Sub
ClearControls()

Dim ctl As Control

For each ctl In Me.formz.Controls
Select Case ctl.ControlType
Case ac TextBox,
ctl.Value=Null
End Select
End Sub

and SAVE as

Private Sub
SaveData()
DoCmd.Close acForm, Me.formz, acSave Yes
End Sub

A pretty long first post I admit but believe me I've looked at so many help pages my eyes are spinning and the forum was my last not first choice. Any help advice or shortcuts to appropriate advice would be appreciated. Unfortunately currently my internet time is severely limited for reasons I cant divulge(No not prison)
Many thanks to those who made it this far into my post - I realise there are some real whizzes who peruse these pages but please remember even Arnie Schwarzenegger was a weed in the gym once upon a time...lol Thanks

 

[d_profesor]

I assume that Urn field is string datatype,

Private Sub Command2_Click()
    Dim Urn_String As String
    Dim vSuffix As String
    
    Urn_String = Nz(DLookup("Urn", "[tbl_y]", "[Urn]='" & Me.txtUrn.Value & "'"), vbNullString)
    
    If Not Urn_String = vbNullString Then
        If MsgBox("Find the record, update record?", vbYesNo) = vbYes Then
            DoCmd.GoToRecord , , acNewRec
        ElseIf MsgBox("Edit Record?", vbYesNo) = vbYes Then
            'Add ur sufix here like:
            vSuffix = InputBox("Add suffix")
            Me.txtUrn.Value = vSuffix & Me.txtUrn.Value
            
        End If
    End If
End Sub

 

[Sandworm]

Thanks for the reply "d profesor" the URN field is indeed a string as number formatting conflicts with the necessity to suffix the numbers alphabetically.
- I'll give the Nz D(Lookup..... a go.

Regarding the second element. I was hoping to automate the process of suffix numbering by the vb code recognising the URN was already in use and as an alternative to:
YES-DoCmd.GoToRecord
was hoping for the NO-that the system could automatically create a new record with the existing number suffixed as the new URN. The code has to recognise that if the number is already suffixed ie 12345C that the new number would be 12345D and so on.
Thanks again.