Macro To Go To TextBox Record (1 Viewer)

Harry Taylor

Registered User.
Local time
Today, 12:36
Joined
Jul 10, 2012
Messages
68
Please help.

I have set a button to open a form [HNumber] with a text box - [Text8]

I want to type a record number [Account] in this box and make my form [Heatersmaster] go directly to that record. (unfiltered)

I have a Macro GoToRecord;
Form
Heatersmaster
Go To
[Text8]=[Heatersmaster100]![ACCOUNT]

([Heatersmaster100] is the data table)

It wont work, and I'm sure i'm miles off but can someone please help.
Sorry is its a noob question :)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:36
Joined
Oct 29, 2018
Messages
21,357
Hi. If you wan to open a different form, then you should use OpenForm instead of GoToRecord. Then, in the WhereCondition argument, you could add the filter/criteria for the account number you entered in the textbox. For example, maybe something like this:
Code:
=[Account Number]=[Text8]
Hope it helps...
 

Harry Taylor

Registered User.
Local time
Today, 12:36
Joined
Jul 10, 2012
Messages
68
Thanks theDBguy.

I tried the OpenForm path but it filtered my database.

So record say 164 of 3000 became record 1 of 1
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:36
Joined
Oct 29, 2018
Messages
21,357
Thanks theDBguy.

I tried the OpenForm path but it filtered my database.

So record say 164 of 3000 became record 1 of 1
Okay, so I guess you're saying you want to keep all the records but simply display the selected record instead. Just to be clear, are we trying to open a completely different form than the one where the textbox is located? If so, I am assuming both forms have the same record source, correct? If so, why do we have two different forms to display the same set of records? If this is not the case, then I guess we just want to display the record entered in the texbox on the same form where the textbox is located, right? If so, then yes, we should use the GoToRecord or something similar, I am thinking something like the SearchRecord action.
 

Harry Taylor

Registered User.
Local time
Today, 12:36
Joined
Jul 10, 2012
Messages
68
I have one main form [Heatermaster].

All I want is to be able to jump to a selected record without users having to go to a tiny Record box at the bottom of the form. I thought text box may be the answer, but there may be a better way.

I'm surprised this isn't a standard feature in Access (2010)
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:36
Joined
Sep 21, 2011
Messages
14,038
Well your assignment is in the wrong direction?
As theDBGuy showed you it should be

Code:
[Heatersmaster100]![ACCOUNT]=[Text8]

I hardly ever use Macros so not sure whether that syntax is absolutely correct, but the assignment certainly is.?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:36
Joined
Oct 29, 2018
Messages
21,357
I have one main form [Heatermaster].

All I want is to be able to jump to a selected record without users having to go to a tiny Record box at the bottom of the form. I thought text box may be the answer, but there may be a better way.

I'm surprised this isn't a standard feature in Access (2010)
Actually, this is sort of a standard in Access. Try adding a combobox on the header area of your form using the Combobox Wizard and select the third option.
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:36
Joined
Sep 21, 2011
Messages
14,038
Firstly I think you need SearchFor Record
Then if you look at the help for that the syntax is the same as if in VBA, in that you need to concatenate the variable with the field being searched.?

Their example is
="Description = '" & Forms![frmCategories]![txtDescription] & "'"

Using it on my test for for an ID it would be
="ID = " & [Forms]![Form1]![text20]

but as you specify the type of object and the name of the object, you can shorten it to

="ID = " & [text20]

GoToRecord is for the likes of Next,Last etc

That works, but I think I will stick with VBA, though I have learnt something, avoid macros . :)

HTH
 

Harry Taylor

Registered User.
Local time
Today, 12:36
Joined
Jul 10, 2012
Messages
68
Thanks theDBguy & Gasman.

I am looking at a ComboBox which does do the job, just need to make it 'pretty' on my form.

I will also investigate the SearchFor Record, but this is a new one for me.

I think the answer (as always) is for me to learn VBA!

Thank guys, you have been a great help.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:36
Joined
Oct 29, 2018
Messages
21,357
Thanks theDBguy & Gasman.

I am looking at a ComboBox which does do the job, just need to make it 'pretty' on my form.

I will also investigate the SearchFor Record, but this is a new one for me.

I think the answer (as always) is for me to learn VBA!

Thank guys, you have been a great help.
Hi. You're welcome. Gasman and I were happy to assist. Good luck with your project.
 

Harry Taylor

Registered User.
Local time
Today, 12:36
Joined
Jul 10, 2012
Messages
68
Solved it :)

Button on my form which opens another form [HNumber]
in [HNumber] I have a Combobox with the following macro
-Search for form
-Form
-Heatersmaster
-First
-="[ACCOUNT] = " & Str(Nz([Screen].[ActiveControl],0))
Close Window

Sweet ;)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:36
Joined
Oct 29, 2018
Messages
21,357
Solved it :)

Button on my form which opens another form [HNumber]
in [HNumber] I have a Combobox with the following macro
-Search for form
-Form
-Heatersmaster
-First
-="[ACCOUNT] = " & Str(Nz([Screen].[ActiveControl],0))
Close Window

Sweet ;)
Good job! Keep it up!!!
 

Users who are viewing this thread

Top Bottom