Find and return value of record primary key (1 Viewer)

[Zydeceltico]

Hi all,

I have a form (frmGetAString) with an unbound textbox (txtUnbound) on it.

That same form has a bound textbox on it named txtStringID which is bound to StringID in tblOfStrings.

The user enters a 6-character alphanumeric string (StringName) from the real world into the unbound txtUnbound.

The alphanumeric string already exists in a table (tblOfStrings) that has two fields: StringID (autonumber primary key) and StringName (short text).

When the user enters the string into txtUnbound, I would like a "search" to happen that returns the StringID of StringName and places that value in txtStringID.

What should I be looking at to accomplish this?

Something to do with recordsets is my first guess. But I can also rationalize how a SQL statement in my code might also work - - -but truth be told - as aware of these possibilities as I may or may not appear - I have no idea how to accomplish it or if there is a better way that I - in my inexperience - am unfamiliar with.

As always - Thank You!

Tim

 

[bob fitz]

Take a look at the DLookup() function

 

[theDBguy]

Hi. Sounds like you want to "look up" a Primary Key value from tblOfStrings and put it in a "bound" textbox to the same field in the same table. If so, you won't be able to do that because a PK field has to be unique and can't have duplicates.

 

[Zydeceltico]

Hi. Sounds like you want to "look up" a Primary Key value from tblOfStrings and put it in a "bound" textbox to the same field in the same table. If so, you won't be able to do that because a PK field has to be unique and can't have duplicates.

I know it sounds funny but it does work already in my situation under a different circumstance.

When the form is opened it is for Data Entry and txtStringID is ready for a "New" value.

Under a different circumstance where I enter a StringName into txtUnbound where my DCount does not find a record I am able to open another form, create a new record, and pass the StringID back to frmGetAString placing it in txtStringID without issue and it records correctly to the underlying table.

 

[Zydeceltico]

Take a look at the DLookup() function

I've been looking at that but it appears that I need a string for the expression whereas - if I am understanding it correctly - I want a numeric value from an ID field.

Would this be a matter of setting DLookup to search the ID field, grab the value as a string and then convert that back to a number?

 

[theDBguy]

I know it sounds funny but it does work already in my situation under a different circumstance.

When the form is opened it is for Data Entry and txtStringID is ready for a "New" value.

Under a different circumstance where I enter a StringName into txtUnbound where my DCount does not find a record I am able to open another form, create a new record, and pass the StringID back to frmGetAString placing it in txtStringID without issue and it records correctly to the underlying table.

Hi. It was just my impression of what you described above. But, if it's working, then I guess I read it wrong. Good luck!

 

[Cronk]

Using the advice in #2, you would have in the after update event of txtUnbound

me.txtStringID = dlookup("StringID", "tblOfStrings", "StringName='"  & me.txtUnbound & "'")

If there is no match, nothing would be displayed in txtStringID

 

[Zydeceltico]

Using the advice in #2, you would have in the after update event of txtUnbound

me.txtStringID = dlookup("StringID", "tblOfStrings", "StringName='"  & me.txtUnbound & "'")

If there is no match, nothing would be displayed in txtStringID

I just tried it to no avail.

I have a DCount statement just before this line of code that tells me that the StringName is already in tblOfStrings which in my test it is but the zero disappeared from txtStringID and was replaced with nothing.

 

[Zydeceltico]

Using the advice in #2, you would have in the after update event of txtUnbound

me.txtStringID = dlookup("StringID", "tblOfStrings", "StringName='"  & me.txtUnbound & "'")

If there is no match, nothing would be displayed in txtStringID

I am receiving a runtime error 2471 "The expression you entered as a query parameter produced this error: 'the value i entered in txtUnbound' "

Pounding my head on this one.

 

[Micron]

I know it sounds funny but it does work already in my situation under a different circumstance.

I think I know what that is from your other post, thus I will say you are talking apples and oranges. This time you want to enter the existing autonumber ID of a record into a bound field. As noted, since that field is bound, this will not work if the field you are trying to put the value in does not allow dupes. If it happens to be an autonumber field, then it does not allow dupes by design, so that's not even something you can over-ride.

Maybe more info on what you have and what you tried for this particular issue vs what you want to end up with would help to provide more focused responses.

 

[Zydeceltico]

I think I know what that is from your other post, thus I will say you are talking apples and oranges. This time you want to enter the existing autonumber ID of a record into a bound field. As noted, since that field is bound, this will not work if the field you are trying to put the value in does not allow dupes. If it happens to be an autonumber field, then it does not allow dupes by design, so that's not even something you can over-ride.

Maybe more info on what you have and what you tried for this particular issue vs what you want to end up with would help to provide more focused responses.

Hi Micron - you would be correct. This one is going the "other way" - but - - - - it's going to take some time to strip it down to where it isn't maddening for someone to just come in and check my specific question - because I do in fact have it working in the opposite direction where I capture the autonumber PK from a different control on a different form and transfer it into the bound control on the first form.

Tomorrow morning I'll strip the db down and post it so you can see what I did ........ and what I'm trying to do.

Thanks

Tim

 

[arnelgp]

if tblOfString is the Record Source of your form, then you are in correct path.
about your hunch on recordset, again, another point.

as a test, add a command button (cmdFind) to your form.
clicking on this button will do the actual search.
on its Click Event, use Code Builder.

private sub cmdFind_click()
if isnull(me!txtUnbound) then
    msgbox "Nothing to search!"
    exit sub
end if
with me.recordsetclone
    .findfirst "[StringName] = " & chr(34) & me!txtUnbound & chr(34)
    ' go to that record if we find it.
    if not .nomatch then
        me.bookmark = .bookmark
    else
        ' inform if not found
        msgbox me!txtUnbound & ", was not found on the table!"
    end if
end with
end sub

 

[jdraw]

Hi Tim,
Let us know if this is still an issue.
If so, a description and example of what you need will help.

 

[Zydeceltico]

Hi Tim,
Let us know if this is still an issue.
If so, a description and example of what you need will help.

Hi JDraw -

Thanks (everybody) for sticking with me.
I was off yesterday and it's been super busy with my regular task list at work today.

I'm hoping to post something in a couple of hours because - yes - it is still a work in progress.

Thanks!

Tim

 

[Zydeceltico]

if tblOfString is the Record Source of your form, then you are in correct path.
about your hunch on recordset, again, another point.

as a test, add a command button (cmdFind) to your form.
clicking on this button will do the actual search.
on its Click Event, use Code Builder.

Hi Arlen - and thank you. I tried your suggestion which makes perfect sense to me - but the form's Record Source is currently a query and the table I need to search is a lookup table and not the table that the final data will be recorded too so the recordset code is not returning anything because there isn't to return from it's record source. But your code and explanation is very clear and I appreciate it. In fact, if I am unable to make my current logic work in the next day or so, I may change the underlying structure of process and relations in order to use your approach. I can see how that is possible.

I would post the db to this db but I was only using Strings as a metaphor and that has gotten me in major flaming trouble in the past So I shan't do it again.

Thank you!

Tim

 

[Zydeceltico]

Hi Tim,
Let us know if this is still an issue.
If so, a description and example of what you need will help.

Hi jdraw - I am going to post an update to my other post because this question - as Micron correctly deduced - is an extension of my previous one which you helped with - which I found a solution for that - this is the next logical hurdle given the way I approached the other question.