Help with a bound Combobox - One change in Combobox changes another combobox

[DaveBre]

All,

I'd appreciate any help that can be offered, I've searched through many pages of posts on Comboboxes but not seem to fit the problem I'm having, either that or I'm missing the answer as I'm an access newbie.

I'm 95% of the way to completing a project to allow payroll staff to log overpayments via an access form (frmoverpayments) which populates tbloverpayments. This is joined one to one with a list of 4000 employees (tblpersonnel), the ID field populates a few other fields on the form via the following code so that the payroll staff don't need to populate all the details.

Private Sub cboEmpID_Change()
Me.SAPID.Value = Me.cboEmpID.Column(0)
Me.FirstName.Value = Me.cboEmpID.Column(1)
Me.Surname.Value = Me.cboEmpID.Column(2)
Me.EmpSubgroup.Value = Me.cboEmpID.Column(3)
Me.Location.Value = Me.cboEmpID.Column(5)
Me.BusGroup.Value = Me.cboEmpID.Column(6)
Me.Email.Value = Me.cboEmpID.Column(4)
End Sub
This part works fine, as does the code I'm written to email employees via DoCmd.SendObject

The issue I have is around a second Combobox on the same form, cboRemOfficer which is a list of 20 people from table tblRemOfficer (who are also included in the larger list of 4000 employees) as they're employees also.

The name of the payroll officer hasn't been saving into the audit database or on the form as it's unbound, but when I change the field to bound it somehow links to the employee field on the form and both combo boxes change at the same time.

No matter what I do I' can't seem to have the second combobox bound and independent.

Any help would be appreciated.

I've attached a winzip version with all confidential info removed etc.

Thanks

David

 

[PeterF]

Both combo's are bound to the same field (SapId) so you get the behaviour you describe.
Bind cboRemOfficer to [Remuneration Officer] and problem solved.

One note to your table tblOverpayments, why store all the info that is in table tblPersonnel. You can easily look it up and have no duplicate data.
An other note: don't yse spaces or special characters in field/table/control names it will bite you sometime.

 

[RainLover]

If you notice how PeterF wrote tblOverpaymnets you should see that it makes reading a little easier. This is called Camel Hump and is used my most people.

The advice about spaces etc is also excellent.

He did not mention that the relationship between these two tables of yours should be One to Many not One to One as you stated.

Unless you have a special reason for doing so all your Forms should be Bound.

tblRemOfficer is not required as it causes you to enter the same information twice. This can and usually does lead to data entry errors.

Instead, add an extra field in tblEmployees and enter the type of employee there. That field should look up values in a special table that holds only this type of data. Even if it is only two or three different types of employees it prevents redundant information.

One last thing is that when posting a sample database, convert it to Access 2003 as not everyone uses later versions and therefore cannot open yours.

Hope this is of some help for the future.

 

[In Nomine Noctis]

Hi guys,

I am new here and I got some serious issues with my comboboxes.

So, in 001 Q Heading query FORM I have 4 different tables/subforms joined toghether.
What I need is:
In 00_4a Component FORM there are stored and setuped informations between 3 different informations = ISSUE, COMPONENT and DEVIATION and those informations are stored in X05X Matrix Issues TAB.

So, in 001 Q Heading query FORM I need filtration based on ISSUE - COMPONENT needs to be filtered based on unique ID (ID Type and IT Component) and also DEVIATION must be filtered based on unique ID (ID Component and ID Deviation).

I´ve seen some help topics here, but I am not able to do that, never coded before in VBA and also, I am not so sure if it will be working between two different subforms.

Any help will be appreciated !
David

 

[RainLover]

Have you ever read about Normalization.

Mt guess id not. So do a Google on normalisation and have a good read. You will have to read it several times to understand. I would suggest looking at more than one version.

If you find one the is complicated then move on to something that you can understand. Microsoft put out a good one.

One you have done that try to fix your table design.

Then give us another look at your database and we can go from there.

Any questions, please ask.

 

[RainLover]

One other thing.

If you click on the blue link in my signature it will take you to some samples. Download "Naming Conventions" this my also help. In fact download anything you want.

Cheers

 

[In Nomine Noctis]

2 RainLover: thanks for your reply.
I heard about normalization much, but from other hand, I am building my database different way (collecting numbers, IDs not text from database size point of view) relationship between my tables are made by forms/subforms via IDs.
Trust me, I already did a lot of databases like this and they are still working without any problem or issue with functionality.

So again, is there any chance to make a VBA code for combobox1 in form1 to filter and activate combobox2 in form2 ?

 

[Mihail]

Why simple if it is possible by complications ?
But is your choice so we must respect this.

So again, is there any chance to make a VBA code for combobox1 in form1 to filter and activate combobox2 in form2 ?

So, you should define one or more public variables in a public module in order to use the AfterUpdate events of Combo1 in order to populate this variables with data from Combo1.
Then define one (or more) public function, also in a public module (you can use the previous created one) in order to return this variables.
Something like this:

Public Var1 'As DataType
Public Function fncReturnVar1() 'As DataType 'Same data type as Var1
   fncReturnVar1=Var1
End Function

As the Combo2 row sources create a parametric query that used, in the Where clause this functions.

As you can see, is doable.
But in your case...

I´ve seen some help topics here, but I am not able to do that, never coded before in VBA and also, I am not so sure if it will be working between two different subforms.

I have huge doubts that you can accomplish the task.

So, as you say, trust me that you must trust in RainLover's huge experience.

Good luck !

 

[RainLover]

2 RainLover: thanks for your reply.
I heard about normalization much, but from other hand, I am building my database different way (collecting numbers, IDs not text from database size point of view) relationship between my tables are made by forms/subforms via IDs.
Trust me, I already did a lot of databases like this and they are still working without any problem or issue with functionality.

So again, is there any chance to make a VBA code for combobox1 in form1 to filter and activate combobox2 in form2 ?

As previously said you can choose your different way of doing things.

Unfortunately I have no experience with the way you do things so I am unable to help.

Best wishes for your project.

 

[In Nomine Noctis]

thx mate

RainLover, just for you, have a look how look like one simple dbase made my way without "normalization"

 

[RainLover]

thx mate

RainLover, just for you, have a look how look like one simple dbase made my way without "normalization"

I don't want to upset you but in spite of what you say you have "Normalised" your Database.

All you need to do now is adopt some better Naming Conventions, Display Your Relationships in the Relationship Window and get rid of Reserved words like Date. Try myDate.

I have a paper on Naming Conventions in the Archives somewhere or click on my SkyDrive link and you could get a copy there.

With proper Naming Conventions you will find that explaining your problem to be so much easier.

I would also recommend using VBA instead of Macros.

You are so close to doing things right. Just take the next step and make life easy for yourself.