Unable to autofill form fields

[bloukopkoggelmander]

Hello all

I am now at the point of going mad as I have been trying to do the below for 2 weeks now so I am desperate need of some help.

I have set up a form with various amounts of combo and text boxes. One of the combo boxes called Username, consits of a dropdown menu from which a user can select Lastname,Firstname ( This filed uses a expression to pull the information together from a table which is linked from a AS400 server, where Lastname and Firstname exists in seperate fields).

Once this username is selected I want other text boxes to automatically fill with the rest of the data corresponding to the slected username like Employee number,Address,phone number,job title etc.

I have tried all sorts of workmethods,differerent code and so forth, but just cannot seem to get it to work.(You would think that a procedure like this would be a common thing to do in Access, but hey I am wrong)

Any help on this would be sooo much appreciated

Thanks in advance

[pks00]

A simple solution might be to create a bounded form to your table (linked)
On selection from combo, load the record (u can use Me.Filter)

[bloukopkoggelmander]

Hi

Thank you very much for your reply. I am new to Access and VBA, so this might take a while , but I will give your suggestion a very good go and see if it works.

Thanks a lot

[NeoPa]

Have you tried a form with a subform?

[bloukopkoggelmander]

Hi there

Yes I did, but either it just did not work or I did something wrong along the line. I also tried using hidden columns, even a worse result.

[bloukopkoggelmander]

Right, hold on guys

I have managed to get hold of someone who actually got this to work. I will get him to explain the procedure he followed and then I will post the procedure on this discussion. Thanks for all your help!!

[MMcCarthy]

I have set up a form with various amounts of combo and text boxes. One of the combo boxes called Username, consits of a dropdown menu from which a user can select Lastname,Firstname ( This filed uses a expression to pull the information together from a table which is linked from a AS400 server, where Lastname and Firstname exists in seperate fields).

Does this correspond exactly to a field value in the record you want to populate

Once this username is selected I want other text boxes to automatically fill with the rest of the data corresponding to the slected username like Employee number,Address,phone number,job title etc.

Assuming the answer to the first question is yes.

Set up the form with the record source set to all records of this type.

Then delete the record source.

in the on click event of the search button put ..

Me.RecordSource = "SELECT [Employee number], EmployeeName, Address, [phone number], [job title] FROM tableName WHERE EmployeeName='" & Me.Username & "'"

[bloukopkoggelmander]

HI there

Well, the first and last name is two different fields in a employee table(Pulled together via an expression).These fileds only changes if a employee leaves or joines the company. The rest of all the fileds on the form I want to autopopulate are all located in the same table as above.

I will give your suggestion a go and see what happens. Fingers crossed.

Thanks

[sinunred]

I have managed to get hold of someone who actually got this to work. I will get him to explain the procedure he followed and then I will post the procedure on this discussion. Thanks for all your help!!

I am attempting to do the same thing as you. You said above that you solved it. What was the method? Thanks.

[bloukopkoggelmander]

Hi there

I thought I did, but not sorry. In a way I am glad to hear I am not the only one. I have now been struggling with this for a very long time. Writing expressions,macros,VBA code etc. I just cant believe that this is such a hard thing to do. I will keep on trying though and when I do eventually find the solution I will make sure to post it to this thread. Please do the same if you find it.....

Thanks (I think I have now developed a nervous twitch from this issue.....:-) )

[bloukopkoggelmander]

Just a note to pks00, thanks for advice. I have tried to do what you suggested, but got so lost trying it. Also to mmcarthy , I have also tried what you suggested, but cannot even get past the following you suggested :

Set up the form with the record source set to all records of this type.

Then delete the record source.

Dont quite understand the working behind that. Sorry if I am sounding a bit of a thicky, but my brain is fried after spending hours after hours on this.

Thanks

[bloukopkoggelmander]

Hi guys

Right I have descided to put the following together :( Unfortunately I have the same posted on another thread as I did a bit of a booboo when I first started listing here and did not mean to post the same problem twice)

Here is a explanation of what I have :

Tablename : Employees
Field name 1 : Employeenumber
Field name 2 : EmployeeFirstName
Field name 3 : EmployeeLastName
Field name 4 : AddressLine1
Field name 5 : AddressLine2
Field name 6 : AddressLine3
Field name 7 : HomeTelephone

On the form I have a ComboBox which dispalys the username(This is done via an expresison which pulls together Filedname 1 & Fieldname 2. ) The users is then suppose to select a name from the dropdown list.

Once the name has been selected, the rest of the Textboxes which corresponds to Fieldnames 1,4,5,6,7 should autopopulate with the data corresponding to the username selected.

Hope this makes more sense of what I want to achieve.

Thanks

[NeoPa]

Then delete the record source.

Dont quite understand the working behind that.

I think what Mary means is :-
Go to design view of the form and clear the current value in the 'Recourd Source' property.
Then, as per the rest of her post, this will be set when necessary, and correctly for the current selection(s), with the code she's posted for you.

[bloukopkoggelmander]

Hi there

Good news, I have done it!! And in a very simple way as well. Here is what I did:

- I created a new query on the table I want to get the data from.
- I then wrote an expression for this query to join the Lastname and Firstname fields as follows:

- FullName: Trim(Tablename.Fieldname for surname) & " , " & Trim([Fieldname for forename])

- I then also included all the fields in the query that will be autofilling the Textboxes I have created on the form.

- I then changed the RecordSource on the form properties to use the new created query .

- For the full name as above, I used a ComboBox and then set the properties for that ComboBox's control source to use the expression I created as above. I then set the following code for the AfterUpdate as follows :

Private Sub (Your combo box name)_AfterUpdate()
Dim rs As Object

Set rs = Me.Recordset.Clone
Dim Test As String
Test = "[The name of the expresion to combine first and last name] = '" & Me![Your combo box name] & "'"
rs.FindFirst Test
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

- I then created all the nessecary TextBoxes I needed and set each individual TextBox's control source properties to point to the corresponding field in the expression above.(This could be selected from the field names presented to you when you click on the dropdown menu for the source).


Presto!!

Now when I select a username, the rest of the textboxes automatically updates with the user's details.

So to all who have helped me, thank you very much.

Also to Sinunred, give this a go, should work fine now.

Thanks a lot guys!

[MMcCarthy]

Good news, I have done it!!

Glad to hear it. It's great when posters take our suggestions and work to come up with there own solutions. That's how your learn.

If you have any further problems, please post them and we'll do what we can to help.

[bloukopkoggelmander]

Thank you very much, all the help much appreciated and I hoped I helped others in the process

:-)

[MMcCarthy]

Thank you very much, all the help much appreciated and I hoped I helped others in the process

:-)

That's what theScripts is all about.

[lefr]

... RecordSource on the form properties to use the new created query .

I'm having a little trouble with this statement. I have been also struggling with this for a while, but if I understand you correctly, when you refer to form properties, are you referring to the properties of the combo box itself? If so, I only show a Control Source and a Row Source under the Data Tab. I can't find the "Record Source".

Thanks

[MMcCarthy]

I'm having a little trouble with this statement. I have been also struggling with this for a while, but if I understand you correctly, when you refer to form properties, are you referring to the properties of the combo box itself? If so, I only show a Control Source and a Row Source under the Data Tab. I can't find the "Record Source".

Thanks

No, the form itself has properties. With the properties open go to the top left hand corner of the window in form design view and there is a little black square in the corner of the frame. Click on that and the properties will change to form properties where you will see things like Record Source.

Mary

[bloukopkoggelmander]

Thank you for the reply McCarthy.

Yip thats correct, each form does have it's own properties.