MS Access Newbie form questions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

MS Access Newbie form questions

Author

Topic

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2006-03-03 : 10:27:55

Hi Gang!
Trying my hand at Access, and having some trouble. I have a table 'STUDENTS' with 4 columns:
IDENTITY (int) unique ID
FST_NAM (varchar) first name
LST_NAM (varchar) last name
FULL_NAM (varchar) calculated field that concats FST_NAM+LST_NAM

I am trying to build a form where we can enter/edit students, and be able to use the FULL_NAM column as a combo box lookup field for existing records. I see that I cannot make FULL_NAM a combo box in the table design (I assume this is due to the formula), so I figure we need to create a view to look up against? I tried that, but I cannot get the combobox to work correctly. it either does not show the proper data or it will not jump to the record if it does show the correct data. I am completely new to Access, this is my first shot at it (whoopie!

). I could really use some help on this!!
The end result when this is all done is I need to create a simple student attendace tracking database to show who was in each class in our Taekwondo school each day, and then be able to report on it. HELP!
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

jhermiz

3564 Posts

Posted - 2006-03-06 : 14:02:34

Dont understand your post. You should never store calculated data into a table, you should present it as calculated data in the presentation layer.

If all you want is a combo box to retreive a "Current Student" that is simple set your combo boxes datasource or record source property to:

SELECT [First Name] + ' ' + [Last Name] FROM YourTable

Its been a while since I've done access so + might be & or something to that effect.

Then in the after update event of your combo box you filter for those records:

"SELECT blah FROM YourTable WHERE FirstName LIKE ' MyComboBox.Value & "%'"

Or you can do a select based on the first name and last name by stripping the ' ' character and storing the string in an array.

Either way this is very easyily done.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-03-06 : 14:42:23

Here is a sample of the code I use for full-name combo-boxes:

SELECT	Persons.PersonID,
	Trim([LastName] & ", " & ltrim([FirstName] & " ") & ltrim([MiddleName])) AS FullName,
	1 AS SortOrder
FROM	Persons
UNION
SELECT	Persons.PersonID,
	ltrim([FirstName] & " ") & ltrim([MiddleName] & " ") & ltrim([LastName]) AS FullName,
	2 AS SortOrder
FROM	Persons
ORDER BY SortOrder,
	FullName;

The use of the UNION query allows each person to be included in the combo box twice; onec as "FirstName LastName", and once as "LastName, FirstName". This allows the user to look up a person either way. The SortOrder colum ensures that selected values are displayed in a consistent format. Set your combo box to 2 columns, with the first column as zero-width. Use the first column (primary key) in your lookup procedure.

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2006-03-06 : 21:56:22

Hey jhermiz,
Sorry for the confusing post, maybe this will help:
I-am-an-Access-Idiot...

I know about not storing calculated data, this was just where I ended up trying to get this thing to work. Apparently I am still a dope, as your post (which sure seems easy enough), still has not got me straightened out. Here is the deal:
I tried making a simple combo box, using the two columns FST_NAM and LST_NAM; when the wizard came up, I set it to select a record on the form. So far so good, the combo box allows me to select a record to work with. Now, I also have a 'Delete record' button on this form, and when I delete a record, the combo box text area still has th old record in it, and the combo box list has it as well. I can get the list to refresh using DoCmd.requery, but I cannot figure out how to get the text box to 'drop' the record that I just deleted. I assume this must be something I need to add to the event procedure for after update, but I am lost. Any help would be most appreciated!

Andy

There's never enough time to type code right,
but always enough time for a hotfix...

jhermiz

3564 Posts

Posted - 2006-03-07 : 08:20:55

You need to requery the combo box after the delete event fires.

Me.cboMyComboBox.Requery

That way your datasoure or recoursource of data in the combo box requeries the values and selects only the new ones. Remember access is attached to a db (as long as you are using a standard ms access db). So when you delete something you have to tell that something to requery itself.

Its a little similiar to the way you have to repopulate a combo box in a c#-sql app.

Jon

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources