How to use Textbox value from one form with another form 2

[0200050]

Hi everybody!

I'm having a little problem when moving from one form to another.

At the first form program requests user to insert a number to a textbox. If the number is known, another form pops up. This second form has a list of numbers.

Now the problem is this: When the second form opens, it doesn't set focus to the right record. I'm looking for a solution how to show the fore-typed record at the numberlist when this second form opens.

I tried already to give some global values to textbox, but Access didn't like it.

I'd really need help at this. Your help would be gratefully appreciated. Thank you in advance,

Mike, finland

 

[afryer]

Hi,

You can get the value of another open form by using the Forms command. On the onOpen event of your second form add the following

l_value = forms ("<form_name>").<textbox_name>.Value

You can then use this to set the value in your second form.

Regards


Andrew

 

[0200050]

Hi Andrew, and thank you for replying!

Your suggestion looks like good, but when trying it out, it still generates an error.

What I now tried to do now when the second form opens was this:

Private sub Form_Load()

Docmd.FindRecord Me!txtRead 'name of my textbox

End sub

Private Sub Form_Open(Cancel As Integer)

Variable = Forms("frmOpen").txtRead.Value

End Sub

And when form is loading, following error occurs:

"Run-time error 2465:"
"Access can't find the field where statement refers"

All I'm trying to find is the same number/record on this second form like i typed in the first one.

What may be the problem when this fore-mentioned error occurs?

 

[afryer]

Hi,

I am a bit confused with the finding of the record, i.e

Docmd.FindRecord Me!txtRead

Why do you need this? you can still extract information from any open form, just by referencing it's controls. If you don't think you need it, then remove it and see if it works then.

Andrew

 

[0200050]

Hi again Andrew!

Hmm.. so you say that by referencing from the second form to the first one this would work out?

So let's see your suggestion with a bit closer look:

l_value = forms ("<form_name>").<textbox_name>.Value

Well, im not any pro on this, so i'd like to get this clear.

Where do i have to set that "l_value"? To the first form? Do i have to give that kind of("l_value") name to my textbox?

And if i put there the name of the textbox, what should i put on this Value-part? It seems it cannot have a property called value... ((.<text.box_name>.Value))

Or am i totally misunderstooding this?`

 

[afryer]

Hi,

l_value is a string variable that you will define in the second form, for example in the onLoad event of the second form:

Sub mySecondForm_onLoad ()

    dim l_value as String

    l_value = forms ("myFirstForm").myTextBox.Value

End Sub

The object myTextBox will be found on the first form.

If it does not work by referencing the Value component then try just referencing the object, i.e:

l_value = forms ("myFirstForm").myTextBox

A point worth noting is that when you are typing the command in then myTextBox will not appear in the list of objects and methods because you are referencing an object outside of the current scope (if that makes sense).

Andrew

 

[0200050]

Oh yeah Andrew i got it!

Now it does move the value from the first form to another. Thank you for your help!

But the main problem which i'm having in my database project, didn't go off anyway...

When the first form opens up, it asks a number. User types a number. The number is for example "123". Then the second form pops in, only, if the number is known. That means if the number exists in a table.(If the number is unknown, program takes no action.)

The second form holds some records from a table.

User can browse the records.

One field at this second form is this numberfield.

When the second form is opening, it searches the record depending on the number given at the first form. Then it shows the right record.

Any ideas how this could be done?

 

[0200050]

So I'm especially now trying to find out a way how second form finds the record, depending on the number given in the first form..

Is there any way to do this?

Mike, finland

 

[afryer]

Hi,

As I understand it there is two ways you can do this:

1) Have a button on your form the check the number and open the form if necessary.
2) Capture the onChange event of your text box so that the program checks after the update.

Personally I would have a button, but it is up to you.

The easiest way to do this is to use the DLookup command. Whichever event you use, button or onChange then the format is the same:

l_text_value = nz (DLookup ("<field>", "<table>", "<value> = " & me.myTextBox), "")

' if the value is empty then exit

if (l_text_value = "") then exit sub

' otherwise open the second form, using the text value

docmd.openform "mySecondForm", , , , , l_text_value

In your second forms load event do the following

dim l_open_args as string

l_open_args = me.openArgs

' now you have the value from the first form, so you populate the second

Hope this helps!

 

[0200050]

Hi Andrew!

I tried that example with a button, but Access generated a nice bunch of errors, such as "You cancelled previous command", and some others.

I'm now a little confused about that "l_text_value" which wasn't declared in your example at all.. Is that meant to be a String?

Or does it mean some object, like textbox?

And again, the "<value>" part of

 nz (DLookup("<field>","<table>","<value>")

is a little bit weird to me. At least its purpose. What value that actually is?


Regards

Mike, finland

 

[KenReay]

Hi

Is the second form a bound form?

There are several mechanisms to move to a specific record:

Look up in help:

DoCmd.GoTo

FindFirst

RecordsetClone

Bookmark

so for example you could say

Me.RecordsetClone.FindFirst "MyNumber = " & txtNumber
If Me.RecordsetClone.NoMatch Then
Else
Me.Bookmark = me.recordsetclone.bookmark
End if

where MyNumber is the name of the column in your table containing the number, txtNumber is the name of the control containing the number you want to look up (so this may well be Forms!Form1!txtNumber for example)





Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[0200050]

Thanks for your reply Ken Reay!

Yeah that RecordSetClone might work, but remember, the textbox is on the first form, so how in earth i could use that recordsetclone -thing on this second form?

- By the way, has someone ever tried to do similar thing itself, like i'm trying here?

Greetings,

Mike, finland

 

[KenReay]

Hi

The typical code I gave you:

Me.RecordsetClone.FindFirst "MyNumber = " & txtNumber
If Me.RecordsetClone.NoMatch Then
Else
Me.Bookmark = me.recordsetclone.bookmark
End if

where MyNumber is the name of the column in your table containing the number, txtNumber is the name of the control containing the number you want to look up (so this may well be Forms!Form1!txtNumber for example)

was assuming this code would be in the second form and the entered value in the first form hence the comment

"txtNumber is the name of the control containing the number you want to look up (so this may well be Forms!Form1!txtNumber for example)"


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[0200050]

Hi Ken Reay!

One thing still confuses me.

At first when program starts, i need to write a number to a textbox, like "txtRead". This number is one part of a record in a table.

Then this textbox, "txtNumber" searches the rest information about the record on a second form, including the same number that was already typed at the first form.

So here i would need to use two textboxes. How i could now use the RecordSetClone thing with two textboxes? I'd like to get it more detailed, if possible.

And that Forms!Form!txtNumber -thing generates an error instantly, 'cause there is something missing.

Greetings,

Mike, finland

 

[KenReay]

Hi

"And that Forms!Form!txtNumber -thing generates an error instantly, 'cause there is something missing"

well it would if you type it incorrectly Forms!Form1!txtNumber is what I gave in my post

I am using example form and control names, you need to substitute your own form and control names

You are over complicationg something which is perfectly simple, I do not even see why you need to use two forms to do this, it could all be accomplished on one form with a couple of lines of code


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[0200050]

Thank you Ken and Andrew, i solved this out now.

Now the program copies the number from the first form to another, and on the another form it finds the number at the Form_Load().

Greetings,

Mike, finland