New to Access - need some help

[poppo101]

I have a form on which I need some help.

1) I am validating an input field against an existing table and displaying an error message if the data entered doens't exist in the table. This works fine. The problem is, that once I respond ok to the error message, the cursor moves to the next field. I want the cursor to remain in the field that is in error.

2) I have another field in which I enter a number and do a lookup against a table that contains the names of all the states. What I want to do is to display the name of the state from the table in a text field on the form. My validation routine is working but I cannot get the name of the state to display.

As I mentioned in the subject line, I am new to Access but have 25+ years programming experience on legacy systems. I could write this in no time at all using what I am familiar with, but that isn't possible. The Access classes that I have taken are no help at all in this real life situation. Nothing like this was even covered. Any help is greatly appreciated.

 

[psemianonymous]

Everyone else recommends the Access [insert version number here] Developer's Handbook - I have no direct experience with any Access book.


The features you ask for are built into Access, in what could possibly be in the help files (but probably not). Thus I recommend the handbook, if this is going to be a long-term thing. As for the answers:

1. There is a built-in feature using a ComboBox that if you set the Row Source to your desired field, and set the "Limit To List" property to Yes, it will automatically pop up an error message and stay on the ComboBox.

Since your code is already working, you can set the focus back to the control of your choice (after the "error messagebox&quot using "ctlControlName.SetFocus" - that will grab the focus back to your input box.


2. Humorously enough, I wrote the following response (below) in another thread, and it applies almost 100% to this thread. Let me preface it though: Access has support for multiple columns in a combobox. this means that (for example) someone could type in "Tennessee", visually you would see "Tennessee" on the form, and the table would store "TN". Or the opposite may happen--fill in the abbreviation, and it stores the full state name.

Aha!  I think I've got what you need.  You want a multi-column combobox.  You want it to store the Agency ID but you want to be able to select via the Agency name?  If so, there are a few steps (none involving VBA):

1.  Find combobox in the form's design view, open the properties box.
2.  For the "Row Source", make a SQL statement that includes both fields.  Aha!  Here it is: 

SELECT tblAgency.AgencyId, tblAgency.FullAgencyName FROM tblAgency

3.  "Bound Column" is set to 1
4.  Now click on the Format tab
5.  "Column Count" = 2
6.  "Column Widths" = "   1";1"     "  - adjust as necessary.
7.  "List Width" = 2"  - adjust as necessary.  Recommend at least as big as the combined Column Widths


Now you should see what you've been wanting to see.



Also, as a side note, you should look up the DLookup function--it basically runs a quick query and returns a single value--good for things such as you originally wanted - but the combobox solution is better.


If I haven't nailed the question as you desired, let me know--I'm still here.


Pete

 

[MIKElaw]

Use Me.Setfocus to push the cursor to the field you want

Use Me.Repaint to redraw the form when you have changed something.

The Access help files contain all the details but that's not much use if you do not know what you are looking for.

Hope the above helps

Mike

 

[AskMan]

1- I think it would be easier to use a combo box.
It's you to define the parameters you need like
RowSource = "SELECT [WhatYouWant] FROM YourTable"
'To display only the records in your table instead of executing your test at each time
LimitToList = True
'To display an error message if if the data entered doens't exist in the source
AutoExpand = True
'To facilitate what is typing

2- For this Point, I think you can look at that

http://www.tek-tips.com/gthreadminder.cfm/lev2/4/lev3/27/pid/702

 

[AskMan]

oops ... too slow !
Sorry
Bye