Autofill

[thanatosuk]

I have created a database that can do 99% of what i want to do.
the 1% is i see as a simple thing (it can be done in asp php ect but i can't figure out how to do it in Access!).

In the data base is a table called table1 it contains details of customers
there is a table called table2 which contains agreements with two bits of information from table 1 the customers name and an ID No (both in separate fields) and other fields such as price agree No etc.

When set up both the name and ID No in table to is entered via drop down boxes (taken from table 1) what i would like to do is as the name is chosen the ID No box takes the related ID No from Table 1 and adds it to table 2.
i have seen some thing similar in the Northwind ensample (chose company info and the email is auto added to its box) but try as I might I can not find how its done code? Macro? or by expression. Every thing else I have done in my DB i have found by reverse engineering or through books but this problem I can not find a simple explanation.

I have attached the DB file again this time I hope it will work

[missinglinq]

I'm sorry you're having problems explaining your problem, but your attached image doesn't appear large enough on my monitor to make out any detail. And when going to unzip your attached "database" Winzip pops an error "No file to extract."

You need to try again and you need to give a written explanation of your problem, not simply posting a graphic.

Linq ;0)>

Modertor

[Jim Doherty]

i have a simple problem or so i thought!
Trying to explain it is getting me nothing but grief so to explain the problem i have a simple DB in access that explains the problem (see image) and it would help if some one can have a look at it an show me how to do it!

From what I can see from your JPG file screen shot you are wanting to grab ID values of one field and maybe store in another and generally trying to get your head around the concepts of combo dropdowns and values they hold etc.

Take a look at this simple db and pull it apart to gain some extra knowledge. It is a very simple thing to demonstrate a table of people and a table of jobs where you assign a particular person to a particular job based on a query that services a form. The form has a query as its recordsource, look how that works then look at listbox and dropdowns to see if it helps you a little. Notice how you cannot click on the listboxs or combos unless a job type is selected first and so on view the simple VBA code behind the form to give you an idea.

It is not the be all and end all in design, but demonstrates a different approach to consider as you progress your learning

Welcome to Bytes :)

[thanatosuk]

ahhhhhhh

Right I cannot find any info in your database that shows me how it is done there is nothing I can see in the code to show how, when you chose the Person assigned in the bottom box, that explains or shows where the person id is entered this is the problem with asking questions I know it can be done cos I see it happening in your DB but how is it done?? Where in the code is the command that will in plain English: "when you pick Donald put his id no in the id no box" do you understand now?

Private Sub cboInformation_AfterUpdate()
On Error Resume Next
If Me.Dirty Then Me.Dirty = False
End Sub

Private Sub cboInformation_GotFocus()
On Error Resume Next
If IsNull(Me!JobType) Then
DoCmd.Beep
MsgBox "Choose a job type first before selecting a person", vbInformation, "System Message"
Me!JobType.SetFocus
End If


End Sub

Private Sub JobType_AfterUpdate()
On Error Resume Next
If Me.Dirty Then Me.Dirty = False

End Sub

Private Sub lstPeople_AfterUpdate()
On Error Resume Next
If Me.Dirty Then Me.Dirty = False
End Sub

Private Sub lstPeople_GotFocus()
On Error Resume Next
If IsNull(Me!JobType) Then
Me!JobType.SetFocus
End If

End Sub

[robjens]

Dude this is extremely easy.

Make sure to include the Id in the first dropdown but hide the column, so two columns Id and Name with their width to 0cm;2cm or something. Bound column is Id. Then you take the OnChange event and set that value

Private Sub lstName_Change()
Me.lstId.Value = Me.lstName.Value
End Sub

[Jim Doherty]

Right I cannot find any info in your database that shows me how it is done there is nothing I can see in the code to show how, when you chose the Person assigned in the bottom box, that explains or shows where the person id is entered this is the problem with asking questions I know it can be done cos I see it happening in your DB but how is it done?? Where in the code is the command that will in plain English: "when you pick Donald put his id no in the id no box" do you understand now?


Private Sub cboInformation_ AfterUpdate()
On Error Resume Next
If Me.Dirty Then Me.Dirty = False
End Sub

Private Sub cboInformation_ GotFocus()
On Error Resume Next
If IsNull(Me!JobTy pe) Then
DoCmd.Beep
MsgBox "Choose a job type first before selecting a person", vbInformation, "System Message"
Me!JobType.SetF ocus
End If


End Sub

Private Sub JobType_AfterUp date()
On Error Resume Next
If Me.Dirty Then Me.Dirty = False

End Sub

Private Sub lstPeople_After Update()
On Error Resume Next
If Me.Dirty Then Me.Dirty = False
End Sub

Private Sub lstPeople_GotFo cus()
On Error Resume Next
If IsNull(Me!JobTy pe) Then
Me!JobType.SetF ocus
End If

End Sub

Yes I fully understand you it is not rocket science sir!

In plain english, the example I gave you does NOT rely on passing the value by code because both combo and listbox are bound to the relevant control they seek to pass a value too.

Having now looked at your db I will deal specifically with what you have (you can maybe understand the contribution I made to you and 'reverse engineer' it in your own time)

In your Form2 your dropdown named 'full' has two columns the first column is ID which is hidden because the columnwidth property is set to zero thus displaying to you only the second column.

You want to pass the value of the ID selected in the 'full' control to the [ID No] control dropdown on your form. To do this in your scenario you need to look at the afterupdate property for the 'full' combobox and insert a code event procedure that is fired when the 'full' combo is updated. The code for that is logical namely, it will only pass a value if there is a value to pass

Do you understand now?

[B]If [/B]IsNull(Me!full) Then
   'do nothing because we are null here on the true side
[B]Else[/B]
  'pass the value because something exists in the full combo
 Me![ID No]=Me!full
[B]End if[/B]

[thanatosuk]

I ow you A bottle of JD!!!

Thanks Jim Doherty, your explanation of how to solve my problem was excellent. All I have to do is change a few bits of data in the db and enter the code you showed me.
It works, and the one thing that bugs me about this problem I could not find the solution in any of the so called bibles for access!


Thanks again

[NeoPa]

a simple question no one can answer! auto fill
I've changed the title of this thread to comply with the site rules.

Apart from being entirely useless as a question, it is also very obviously illogical. If it were simple, not only would everyone be able to answer it, but you wouldn't need to post it in the first place. As a general rule, one can only decide if something is simple when one knows what that something is. When an answer exists, then it can be categorised.

Please try to give a little more consideration to the rules (common sense mostly) before posting a question in future.

Administrator.

[Jim Doherty]

Thanks Jim Doherty, your explanation of how to solve my problem was excellent. All I have to do is change a few bits of data in the db and enter the code you showed me.
It works, and the one thing that bugs me about this problem I could not find the solution in any of the so called bibles for access!


Thanks again

Glad I could help you :)

[thanatosuk]

in reply to Neopa
I made the question simple because the problem I had was in a large DB, and for 4 days I could not find the answer to the problem. Either in Books on Access or on other forums only Jim took the time to answer the question with patience and in a way that was understandable. from his answer i have learnt more than just the solution that i wanted. one considerate person who looked at the problem has improved my ability 10 fold.
As to your statement ‘it is also very obviously illogical’ is blown out of the water by jims solution I knew it was possible to do I just did not know how.

[NeoPa]

thanatosuk: As to your statement ‘it is also very obviously illogical’ is blown out of the water by jims solution I knew it was possible to do I just did not know how.

You misunderstand my point.

I was saying that it is illogical to indicate a question is simple when you don't already know the answer. A simple question is a question that is easily answered. As you don't (didn't) yet have an answer you are not in a position to know whether or not it is simple.

As a point of interest, people don't generally appreciate being told that their efforts to provide a solution are of a basic nature - especially by someone who is in a position where they need to ask it. If you give that a little thought you shouldn't find it too complicated a point to grasp I'm sure.