Access 2000 Forms (SR-1)

Topic

New Reply

I am having problems with information on forms. When I search for a student, I enter the students first name and then last name. The first form that pops up has the correct student name, when I go onto the next form, the students first name is incorrect but the last name is correct. It seems that if the student has a sibling within the same school, the second form grabs the first name of the sibling who is first in the alphabet. Here is an example of what I mean: Robert Smith and Amanda Smith both are students at the same school. I search for Robert Smith, on the first form, Robert comes up in the first name field, and Smith comes up on the last name field. When I go onto the next form, Amanda pops up in the first name field and Smith in the last name field. This is also what happens on the other 4 forms attached. Can someone please help me! I am going crazy trying to figure this out! Thank you!
Wendy

Reply | Quote

Replies

I am not sure how you are going about your search. Are you are using combo boxes to list last and first names? Text boxes? Do you have a query that gets its criteria from the combo or text boxes? If you are using combo boxes or text boxes and you have criteria something like this in the Criteria field of your query: [Forms]![YourFormName]![YourTextBoxName] then be sure that they are on the same line so that the query is doing an AND search. For example LastName = Smith AND FirstName = Robert. This way you will only find the Robert Smith’s but no Amanda’s….

A bit more detail on how you are selecting and looking for your records will help someone here to give you a more complete and accurate answer.

Jack

Reply | Quote

I am using text boxes to list first and last name. The information for the form is coming from a query. In the query, under the criteria for first name, I have [Enter First Name]. Under the criteria for last name, I have [Enter Last Name]. The user enters this information, they still get incorrect information on the second, third, forth and fifth forms. What am I doing wrong?

Thanks
Wendy

Reply | Quote

Hi Wendy,

I’m a bit about the way you are trying to make this work. It sounds as if either your query has the first name and last name on different lines of the query grid, or the first name field isn’t really bound to the query. And I’m not sure I understand how the form is working. Are you using the standard Access record navigation controls to step though the records, or are you clicking a button that causes the query to be rerun? If its the former, then run the query by itself and look at the records that are returned. If it’s the latter case, run the query the same way and make sure you are getting only one record. Hope this helps.

Reply | Quote

Hello

I didn’t make the database, I just am stuck trying to make sure it is working. The way the query is set up is with the first name and last name in two seperate fields, which would make them on different lines of the query grid. When they go into the form to search for a student, a prompt pops up asking them to enter the first name and then another prompt pops up asking them to enter the last name. Then in a perfect world, the students correct information would show up on all the forms. As it is right now, the students correct name is only on the first form, and the rest of the forms have the incorrect first name if there is another student in the same school with the same last name. Do you understand what I am trying to explain?
Thanks for the help!
Wendy

Reply | Quote

I think I now understand what you are saying. What is happening is that the first form comes up with the right student but when you click on a command button to go to another form it shows all students with the last name that was selected the first time round. If you look at the code in the button that opens the next form it probably says something like: DoCmd.OpenForm “FormNameHere”, , , “[LastName]= ‘” & Me![LastName] & “‘”. You need to change that so it reads like this:

“[LastName]= ‘” & Me![LastName] & “‘ And [FirstName] = ‘” & Me.FirstName & “‘”

The quotes are a bit hard to read so here it is again with extra spaces… (Don’t use the extra spaces)

“[LastName]= ‘ ” & Me![LastName] & ” ‘ And [FirstName] = ‘ ” & Me.FirstName & ” ‘ ”

Let us know if we are on the right track for you….

Jack

Reply | Quote

OK – one fundamental question: Is there a button you click that runs some code, and does that code include a SQL string, as Jack suggests, or does it simply do a requery on the form?

My suspicion is that you have the two parameters for the query on on different lines (rows) in the query grid so that you are getting an OR condition in the SQL, when what you want is an AND. To get the AND, they have to be on the same line in the grid. If the SQL is doing an OR, then you will get more than one record if two or more students have the same last name. Note that the name should be split into two fields, which puts them into two separate columns (not lines) in the grid.

Another solution we sometimes use is to use an unbound drop-down combo box to display a list of all the students – when you select a particular student, the wizard in Access will apply a filter that displays the info about that particular student. That might be a simple alternative that solves your problem. Hope this helps clarify things.

Reply | Quote

You are right, I am getting the OR condition and I want the AND. Could you please explain to me how to use the unbound drop-down code on the form? Is there a way to set it up so if you typed in the last name Smith, all students with the last name of Smith in that particular school would come up and you could choose the one you want?
Thanks for all of your help!
Wendy

Reply | Quote

The basic trick is to look up the names from whatever table they exist in, and display them in a combo box. The simplest way is to let the Access Wizard do the work for you. In the form you want to display information about each student you have all the fields being displayed, typically in bound text boxes. Then go to your Access Toolbox and make sure the Wizard (the button with the magic wand) is active. From the Toolbox select the Combo Box button, and then click and drag a combo box on your form. That will open the Combo Box Wizard dialog box which gives you three choices. Choose the bottom one (Find a record on my form based on ….) and click the Next button. The Wizard then displays the list of field names for the data source of the form, and asks you which ones you want to display. Pick the LastName and FirstName fields (and any others appropriate such as the primary key) and click Next. Then the Wizard lets you set the size of each column – more about that in a bit – click Next. Click Next again and it proposes a label for the combo box control and asks you to click the Finish button. When you do, you will have a combo box that has code associated with the “After Update” event that will let you select a particular student by dropping down the list of students and select one of them.

Now to the esthetics of the situation. In general you will want a unique identifier for each student – we usually assign an autonumber field, though it could be a SSN, a passport number or what ever. The idea is that names are not necessarily unique – I know of at least three people who share my exact same name here in the US, and my name is not a common one. So the trick is to use that as the first column in the combo box, and set that column width to zero (0) so that the name field is always displayed. To make it easier to use, instead of displaying the Last Name in one column and the First Name in a second column, you may want to use a query as the combo box data source, and concatenate the names together in the form
FullName: [LastName] & “, ” & [FirstName]
That way you can type a few characters of the LastName and very quickly get the combo box close to the name you want to select. The Help file that is offered on the last page of the Wizard is actually fairly useful.

Combo boxes work pretty well for this sort of thing with up to several hundred things to choose from. Beyond that you may find it is pretty sluggish with machines that have limited resources. In those cases (several thousand records) we typically build a special search form, or we use a treeview control. Hope this gets you started with using combo boxes.

Reply | Quote

I would like to thank both of you for all of your help! You are both wonderful! I can now get both the first and last name on all of my forms. Now the only the message that pops up occassionaly is “Write Conflict”. I told my users I think this happens because the forms are set to no record lock and when one or more people are changing the information, they will receive this message. Am I correct in assuming this?

Thanks
Wendy

Reply | Quote

Hi Wendy,

Thanks for the kind words – this is a great resource, isn’t it! As to your “Write Conflict” message, those shouldn’t happen very often unless you have several users entering or editing data at the same time. If they happen more than VERY occasionally, you may have something else going on. Access 2K does support record locking rather than page locking as prior products did, in which case you might look at that if the problem continues to happen once things really get going and you have a fair bit of data in the system. Any how if the message is anything more than intermittent post again and we will try to help. Cheers!

Reply | Quote

Hello

As my luck would have it, I am getting the “Write Conflict” error at all 4 schools. Here is how our database is set up. We have a back end database that houses only the tables and each individual school has a copy of the database housed on their local servers which contains the queries, forms and reports that pertain to that school. Each schools queries, forms and reports are linked to the main back end database tables. When they try to update a student record and someone in another school is also in their database doing the same function, the “Write Conflict” pops up on all of their screens. When they select the option to save the record, it does not save the information they changed. The way the form they are using is set up is to search for the student but it does allow data entry. My question is should I set up a control button which will save the record upon updating a form to take care of this problem? I have an Access book which said I could lock the records to prevent this problem but my concern is that if I do this, it will not allow all four schools to access their forms at the same time. I could be completely off base here because I am afraid to lock the records since all data goes back to the main table on the back end database I am concerned that would only allow one school at a time to access the information at a time. I would appreciate any help you can give me. As much as I am enjoying furthering my knowledge in Access, listening to 6 women scream at me all day is not my idea of a good time!

Thank you once again!
Wendy

Reply | Quote

HMMMMM – you might check your form to make sure it doesn’t have some sort of record locking set that is causing the problem. If any two people try to edit the same person, you will certainly get that conflict, but if they are working on different records, it should happen pretty infrequently. I think you might want to change the default locking mechanism to use record locking rather than page locking as well. That way you should get a conflict only if users are trying to edit the same record, not one that just happens to be close by and on the same page. Let us know how it goes – 6 women screaming is not much fun at all, 1 usually exceeds my tolerance level.

Reply | Quote

I did a bit more research on your problem, and realized that I was assuming that you were using an Access back-end database (at the central site). Is that correct, or by chance is it a SQL Server or MSDE data file? In case it is, you might want to look at this technet article:
If">Q238258 and Q225926
Since you are using SR-1 you shouldn’t have a problem with starting the database from the desktop using a shortcut, but who knows. Another thing to check is to make sure that nobody is trying to open the database in an exclusive mode. Also if your primary table only contains 100 to 200 bytes, you may definitely want to set up to use “record locking” mode rather than “page locking” mode. The help file gives fairly good info on how to change the behavior of the database, and it will need to be done in both the front and back-end databases. Hope this helps.

Reply | Quote

Wendell

All I can say is that you are a genius! Those articles you sent help me more than I can say! I am also happy to report that I have not been screamed at for 5 hours now! All four schools are entering information and so far, the “Write Conflict” only appears once in a “blue moon”. They are now able to save all the information they entered and they are happy, happy women!

Thank you once again! You are a life saver!
Wendy

Reply | Quote