Macro To Go To TextBox Record (1 Viewer)

[Harry Taylor]

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]

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:

=[Account Number]=[Text8]

Hope it helps...

 

[Harry Taylor]

Thanks theDBguy.

I tried the OpenForm path but it filtered my database.

So record say 164 of 3000 became record 1 of 1

 

[theDBguy]

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]

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]

Well your assignment is in the wrong direction?
As theDBGuy showed you it should be

[Heatersmaster100]![ACCOUNT]=[Text8]

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

 

[theDBguy]

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]

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]

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]

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]

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]

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!!!