Edit Record 2

[Nene75]

Hi Everyone:

I am trying to edit the record by selecting the last name of the record. I would like the matching record open where I can make modificaitons. But when I select the last name from the drop down menu I am getting blank Data entry form rather ... what am I doing wrong?

Please help!

Thanks!

 

[docmeizie]

Two things you will want to check right off the bat are:

a) Is your form in edit or add mode,
b) Is your data correctly binded to the form so that the query or table is showing all records.

Also you may not want to just do a record search by just the last name, you will get multiple records if people have the same last name, thus making you have to search through each of the records with the same last name. You may want may want to create a combo box with the ID and an expression like

Fullname: FirstName & " " & LastName

or

LastFirst: LastName & ", " & FirstName

. In the After Update event put this code:

Me.RecordsetClone.FindFirst "[WhateverID] = " & Me![NameofComboBox]
Me.Bookmark = Me.RecordsetClone.Bookmark

If I take a peek in your Windows, to fix a problem, does that make me a "Peeping Tom"? Hmmmmmmmmmmv

 

[rdodge]

What information you have provided is too vague as there's too many questions that can be asked before we could give a clear answer. Examples:

Is/Are your form(s)/control(s) bound to the data?

Do you have a sub form involved?

If you form(s)/control(s) is/are not bound, how are you getting the data? Jet via linked tables, DAO coding, and/or ADO coding? This question is also assuming you are using a FE/BE DB system as highly suggested with using Access in a multiple user environment. Are you using 2 different recordsets for this task?

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[Nene75]

docmeizie:

Thanks for the prompt reply.

a) The form is in edit mode.
b) I have a main form with 5 subforms.

In the After Update event I have the following code:

'DoCmd.OpenForm "frmEditApplicantData", , , "[ApplicantLastName] = '" & _
'Me.txtLastName & "'"

------------------------------

Ronald:

Thanks for the prompt reply.

Sorry for posting the vague information.

Yes the main form has 5 subforms. The subforms are linked with the main form.

------------------------------

 

[rdodge]

Just as docmeizie mentioned, I would also use some sort of an ID field to link the different tables, which then your main form/sub form should follow suit. Take telephone numbers for instance. Now days, one individual could have several phone numbers (Work, Home, Emergency, Cell, Fax, Modem, and Pager, that's 7 right there). Back in the olden days, database system were built with maybe up to 4 telephone numbers, but now, with an individual could have 7 telephone numbers, rather than adding more fields, it would be redesigned to have a separate telephone number table. What this example gets at is data normalization form. It's cheaper to add records than it is to add fields.

With this example, you would have the following structure:

Individual Table:

Ind ID
First Name
Middle Initial
Last Name
Title
Suffix

Type Of Phone Table

Phone Type ID
Description

Phone Table
Ind ID
Phone Type ID
Phone Number

From this point, if you are wanting to look up someone's phone, you can create a SELECT QUERY to lookup information based on either the first name or last name or both, along with the type of phone.

Me, I don't work with bound forms/controls cause with the way the Events are setup, it doesn't allow for the user friendliness of the form for those that are predominantly mouse users and still have the strict data validation checks that I require. Therefore, my knowledge is somewhat limited with regards to how other aspects of bound objects works. I use DAO and VBA coding to do the tasks that I require.


Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[docmeizie]

Okay are your subforms coming up empty or is the main form coming up empty. If your subforms are coming up empty then your main and sub forms are not connected right and you would have to take a look at the number that links all the data together. If that is correct then check your relationships to see if all your tables are correctly connected to each other.

Going back to the last name thing for a second, the code i mentioned in my other post just allows for you to further search by the name by going into the first name also. Say for instance you have 4 people with the last name Smith. Well Ray Smith, Dawn Smith, Michael Smith, and Ronnie Smith all have different records. How would you distinguish which record exactly that you wanted to go to? If you use the LastFirst version in your code then you would be able to just type the last name and the first letter of the first name to get to the appropriate record. Just an FYI for a more friendly interface.

If I take a peek in your Windows, to fix a problem, does that make me a "Peeping Tom"? Hmmmmmmmmmmv

 

[rdodge]

One thing I should of added to the Phone Table was a Phone ID, so that table would really be:

Phone Table
Phone ID
Ind ID
Phone Type ID
Phone Number

Main purpose for this, each record would have it's own unique ID number

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[Nene75]

rdodge and docmeizie:

Thanks to both of you for the prompt reply and through explaination of table structure and DB interface.

Let me explain what I have for the table relationships:

Main Table
tblApplicantBioData
- ApplicantID
- FirstName
- LastName
- MiddleInitial
- CurrentStreetAddress, CurCity, CurState, CurZip
- PermanentStreedAddress, PerCity, PerState, PerZip
- HomePhone
- WorkPhone
- CellPhone
- EmailAddress

tblApplicantEducationInfo
- ApplicantEducationID
- ApplicantID (Foreign Key)
- SchoolName
- SchoolCity, State, Zip, Country
- DegreeObtained
- DegreeMajor
- Skills

tblWorkExperience
- ExperienceID
- ApplicantID (Foreign Key)
- YearsofTotalWorkExp
- YearsofExpinJobAppliedFor
- GovernmentExp
- YearsofGovtExp

tblLocationPreference
- LocationID
- ApplicantID (Foreign Key)
- Geographic Preference
- Relocate

tblEEOInfo
- EEOID
- ApplicantID (Foreign Key)
- Gender
- Ethnicity
- Disability
- Veterans

tblResumeSubRevInfo (Resume History)
- ResumeSubRevID
- ApplicantID (Foreign Key)
- ResumeNumber
- ResumeSubmissionDate
- ResumeRevisionDate
- ResumeReviewedBy

tblResumeFile
- ResumeID
- ApplicantID (Foreign Key)
- ResumeFile (Hyperlink to DOC, RTF file)

-------------

tblApplicantBioData has ONE-TO-MANY relationship with tblApplicantEducationInfo and rest of the talbes have ONE-TO-ONE relationship.

Docmeizie: Yes you're right I would prefer to have search the record with both last name and first name together. All of the forms are empty when perticular record is selected.

I can't figure out what am I missing here ...
Help me please ...

Thanks!!!!

 

[docmeizie]

This is one thing that I usually try when it seems that all is not right and data will not show up. I usually create a query through Access's query wizard with all the fields of the connected tables for a form. I run the query and see if I get the same result. If I get the same result then I will remove one table from the design of the query and rerun it to see which table is causing the problem. You'll know which table is causing the problem because after you remove it and rerun the query data will be present. The table you removed is thus the problem. So you might want to check that table and it's link properties and relationships. I have solved all blank form problems this way.

If I take a peek in your Windows, to fix a problem, does that make me a "Peeping Tom"? Hmmmmmmmmmmv

 

[Nene75]

docmeize:

I am going to check the table relationships and will get back to you. Thanks for helping me out.

Thanks

 

[Nene75]

Docmeizie:

I checked all the tables and realationships. If there were no data in the related tables then I would get the blank record but if there is data in the related tables then the query shows the record. So all tables and links seem to work fine. But I am still getting the blank form when I want to search the record based on the Last name, First Name selection.

Please help me!

Thanks!

 

[Nene75]

Hi guys:

I got the Edit funcationality working fine by having the following code on the After Update event of the Record Selection for modification:

Private Sub ApplicantLastName_AfterUpdate()
DoCmd.OpenForm "frmEditApplicantData", , , , , , Me.ApplicantLastName.Value
End Sub

And on the form that opens with the selected record I have the following code on OnLoad Event:

Private Sub Form_Load()
Me.Filter = "ApplicantLastName = '" & Me.OpenArgs & "'"
Me.FilterOn = True
End Sub

Thanks Docmeizie and Rdodge for your effort and interest in helping solve the problem.

 

[Nene75]

docmeizie:

I concatenate the First Name and Last Name as a FullName:

SELECT [tblApplicantBioData].[ApplicantFirstName] & " " & [ApplicantLastName] AS Fullname FROM tblApplicantBioData;

But I am unable to recieve the selected record. I get blank data entry from rather. How do I correct this?

Thanks for any help provided.

 

[docmeizie]

Try checking to see if the fullname is indexed, but I think you may have to go from the Primary ID of the table. Try that I and let me know how it goes, in class right now so I will check back soon.

If I take a peek in your Windows, to fix a problem, does that make me a "Peeping Tom"? Hmmmmmmmmmmv

 

[Nene75]

Docmeizie:

I have set the FullName sorted as an ascending order in the underlying query. I didn't get what you mean by Indexed?

 

[rdodge]

Within the table that your information is stored, go into Design View, then click on the Index button. It will list all indexes in there including the "PrimaryKey" index, should you have set something as the primary key of the table.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[Nene75]

Thanks Ronald for the prompt response.

In the PrimeryKey Index property its set to the following:

Primery: Yes
Unique: Yes
Ignore Null: No

 

[rdodge]

When you are in the design view of the table, click on the toolbar button that has the lightning bolt on it which is to the right of the toolbar button that had the key on it. If you click on that, you will see the Index Name, Field Name, and the Sorting Order. For indexes with multiple fields, it will only list the index name on the same record as the first field of the listed fields for that particular index while each of the remaining fields will be listed directly below without anything in the Index Name field.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[Nene75]

Ok This is exactly what shows under the Indexes window:

Index Name
1. ApplintID
2. JobID
3. PrimaryKey

FieldName
1. ApplicantID
2. JobID
3. ApplicantID

SortOrder
1. Ascending
2. Ascending
3. Ascending

Index Properties:
For 1.ApplintID and 2.JobID Indexes the properties for Primary, Unique and Ignore Null are set to NO.

For 3.PrimaryKey Index the properties for Primary and Unique are set to YES and Ignore Null is set to NO.

 

[docmeizie]

Are all your records blank. For that you may want to try see if you have the forms data entry set to data entry. Cut that off if you have it on. Second: are you trying to search by the fullname or just display it?? If the former then just create a locked combobox with first column having a hidden prim. key and the second column with the fullname. Then add some search code to it. If it is the latter, you want to check and see if it is tied to its control correctly.

If I take a peek in your Windows, to fix a problem, does that make me a "Peeping Tom"? Hmmmmmmmmmmv