using macros to open specific record in a form

[JCC]

I am completely new to Access but after reading a book "Database Projects in
Access for Advanced Level" I have managed to make a good database for my
business. However, I am stumped by what seems a simple idea. I have a Clients
Form with Id; 1st Name; Surname and Address and I would like a button on the
switchboard that would allow me to input a Surname and for the Clients form
to open at that specific record.
Is this possible using Queries and or Macros?
Answers would need to be simple as I don't know VBA.

Thanks for any help!

 

[AndyB via AccessMonster.com]

Hi There,

The simplest way of doing this is to use a Combo box to find the appropriate
record.

Open your form in design view.
On the Toolbox Toolbar, find the 'Combo Box'.
Click on it, then click on some spare space on your forrm,
This will insert a combo box and bring up a dialog box where you can select
the option to 'Find a record on my form.....'
Once you've selected this & clicked next another dialog will appear with two
windows. (Available Fields & Selected Fields)
Click on the Surname Field, then click the right arrow between the windows.
I would suggest you also select the Christian Name field, just in case you
have 2 entries with the same surname.
When you click next, a view of the selected fields will be displayed with the
option of resizing the columns as appropriate.
When you've resized & clicked next, you can label your combo (Something Like
'Search Surname')
Click finish & you're done.
Save the form, open it in normal view.
You can now select the entry you want in the combo & the form will jump to
the record.

To sort the combo in ascending order:
Open the form in design view, click on the combo and view it's properties.
(If the properties window isn't open, right click the combo & select
properties).
Click on the 'Record Source' property & a small square with 3 dots will
appear to it's right.
Click on this square and the SQL Statement Query Builder will appear.
Your selected combo field(s) will be seen in the query builder.
In the Surname column, find the 'Sort' row.
Click in the cell and a down arrow will appear.
Use the down arrow to select Ascending.
Do not try to save the query as this will save it as a separate query.
Simply close the builder using the Close button on its top right.
You should then be asked if you wish to save changes to the SQL Statement &
Update the property.
Click Yes & you're done.
Save the form, open in normal view & your combo box should now sort the
surname in ascending order.

Have Fun,

Andy

 

[JCC]

Hi There,

The simplest way of doing this is to use a Combo box to find the appropriate
record.

Open your form in design view.
On the Toolbox Toolbar, find the 'Combo Box'.
Click on it, then click on some spare space on your forrm,
This will insert a combo box and bring up a dialog box where you can select
the option to 'Find a record on my form.....'
Once you've selected this & clicked next another dialog will appear with two
windows. (Available Fields & Selected Fields)
Click on the Surname Field, then click the right arrow between the windows.
I would suggest you also select the Christian Name field, just in case you
have 2 entries with the same surname.
When you click next, a view of the selected fields will be displayed with the
option of resizing the columns as appropriate.
When you've resized & clicked next, you can label your combo (Something Like
'Search Surname')
Click finish & you're done.
Save the form, open it in normal view.
You can now select the entry you want in the combo & the form will jump to
the record.

To sort the combo in ascending order:
Open the form in design view, click on the combo and view it's properties.
(If the properties window isn't open, right click the combo & select
properties).
Click on the 'Record Source' property & a small square with 3 dots will
appear to it's right.
Click on this square and the SQL Statement Query Builder will appear.
Your selected combo field(s) will be seen in the query builder.
In the Surname column, find the 'Sort' row.
Click in the cell and a down arrow will appear.
Use the down arrow to select Ascending.
Do not try to save the query as this will save it as a separate query.
Simply close the builder using the Close button on its top right.
You should then be asked if you wish to save changes to the SQL Statement &
Update the property.
Click Yes & you're done.
Save the form, open in normal view & your combo box should now sort the
surname in ascending order.

Have Fun,

Andy

However...!! I couldn't stop playing around with macros after I posted my
question, and I have come up with one that will work for a surname search
but as you say, this could be a problem.
I used OpenForm and put [Surname]=[Enter Name] in the 'Where Condition'.
Is it possible to amend this to do a 1st and surname search?
Would I need an 'Add Names Together' query to run first?

Thanks again
Jan

 

[AndyB via AccessMonster.com]

Hi Jan,

Yes you can extend the 'Where Condition' to read as follows:

[Surname]=[Enter Name] And [1st Name]=[Enter 1st Name]

However, this is not good practice, requires inputting the names in two
inputting boxes.
Any slight misspelling will result in the record not being found.
In addition, this macro will filter the records, so that only the searched
record will be available.

The combo box solution is far more practical as you are selecting the names
from the table, so no chance of spelling errors and all records are still
available.

I know my instructions looked complicated, but it's not half as difficult as
it looks.

Have a go - you've got nothing to lose & lots to gain.

Andy B

Hi There,

[quoted text clipped - 53 lines]
However...!! I couldn't stop playing around with macros after I posted my
question, and I have come up with one that will work for a surname search
but as you say, this could be a problem.
I used OpenForm and put [Surname]=[Enter Name] in the 'Where Condition'.
Is it possible to amend this to do a 1st and surname search?
Would I need an 'Add Names Together' query to run first?

Thanks again
Jan

 

[JCC]

Hi Jan,

Yes you can extend the 'Where Condition' to read as follows:

[Surname]=[Enter Name] And [1st Name]=[Enter 1st Name]

However, this is not good practice, requires inputting the names in two
inputting boxes.
Any slight misspelling will result in the record not being found.
In addition, this macro will filter the records, so that only the searched
record will be available.

The combo box solution is far more practical as you are selecting the names
from the table, so no chance of spelling errors and all records are still
available.

I know my instructions looked complicated, but it's not half as difficult as
it looks.

Have a go - you've got nothing to lose & lots to gain.

Andy B

Hi There,

[quoted text clipped - 53 lines]

Thanks for any help!

However...!! I couldn't stop playing around with macros after I posted my
question, and I have come up with one that will work for a surname search
but as you say, this could be a problem.
I used OpenForm and put [Surname]=[Enter Name] in the 'Where Condition'.
Is it possible to amend this to do a 1st and surname search?
Would I need an 'Add Names Together' query to run first?

Thanks again
Jan

--
Message posted via AccessMonster.com


Thanks Andy,

I was just curious - but we know where that leads!!
I have gone with your first suggestion and it's working fine.

Really appreciate the help

Jan