Combo Box with variable default...........

[Robert88]

I was wondering if somebody can help with my default combo box problem?

I have a secured database whereby users logon, which is used as a scratch pad for keeping track of Purchase orders. A Form frmUserInput with two relevant fields for this problem 1. User Initials txtUsersInitials and 2. POBook (Purchase Order Book) which is the combo box cboPOBooks both unbound fields on the form.

The txtUsersInitials is generated automaitcally when the form is loaded using a VBA statement txtUsersInitials = Current User.

The list of the combo box is generated from a table tbleUsersPOBook and uses a query qryUsersDefaultPOBook as listed below;

SELECT tblUsersPOBook.UsersPOBookName
FROM tblUsersPOBook
WHERE (((tblUsersPOBook.UserName)=[Forms]![frmUserInput]![txtUsersInitials]));

This generates a drop box showing the Different department codes for each individual. So for some it is only one item for others up to three codes, this part works fine...

However here is my problem.

Since some people can sign off more than one Purchase Order book I have had to add an additional field in the table tblUsersPOBook called DefaultUsersPOBook which is a yes/no field, so only one yes exists for each user who logs on. I have created another query qryDefaultPOBook as listed below;

SELECT tblUsersPOBook.UsersPOBookName
FROM tblUsersPOBook
WHERE (((tblUsersPOBook.UserName)=[FORMS]![frmInputForm]![txtUsersInitials]) AND ((tblUsersPOBook.UsersPOBookDefault)=Yes));

When I place this query in the Default section of the combo box it gives me no result.

Firstly is it possible to have a varying default like this since each user will have a different default?

Or is there maybe a better way of achieving a default which is variable for each user based on thier default Purchase order book in the table tblUsersPOBook for the field "Yes" in DefaultUsersPOBook ?

 

[Robert88]

Still scratching my head occasionally over this one, can anyone help?

I will see if I can make it easier?

I have added another table tblUsers. Within it I have fields, Username, UserPOBook & DefaultPOBook which are the most important for this.

to give an example of its contents as below

Username,UserPOBook,DefaultPOBook
Neil, MAR, YES
Robert, ITE, YES
Neil, HRM, NO

So when "Neil" logs in I need from the info in the tblusers above a combo box for Neil to default to MAR (since his DefaultPOBook is YES) and have a combobox with MAR & HRM within it. Of course if I ("Robert") log in it should only have ITE as default within a combo box (since I only have ITE as my DefaultPOBook as YES) showing only one item in the combo box on a form but defaulted to this one value already.

Currently Upon load of the form a field on the form exists which places the current user in it with

Private Sub Form_Load()
Dim dUsersInitials As String
' attain dUserInitial
dUsersInitials = CurrentUser
End Sub

I am wondering if it is possible to incorporate the dUserInitials into a query to make the default value for this combo box and if this is possible? Or maybe there is a better way, currently open to suggestions.

Look forward to anyones comments.

 

[Robert88]

Thanks for everyones help, only joking, LOL!!!

I have tried a few more things to see if this works, getting a little closer???

I have tried to use the following command on a Form Load in the combo box cboPOBook.

Private Sub FormLoad()
Dim dUserInitials As String
dUserInitials = CurrentUser
txtUserInitials = dUserInitials

cboPOBook.DefaultValue = DLookup("[UserPOBook]", "tblUsers", "[UserName] = Forms![frmInputForm]!txtUsersInitials and DefaultPOBook = -1")

End Sub

But it does not work it shows me a "#Name?". I am thinking maybe I should try an event in the combo box cboPOBook as it might occurr after the dUsersInitials has been loaded and stored into txtUsersInitials? I am not sure and not sure which event I should possibly use if I was to do this?

Can anyone help?

What is more strange is that if I use this command [cboPOBook.DefaultValue = DLookup("[UserPOBook]", "tblUsers", "[UserName] = Forms![frmInputForm]!txtUsersInitials and DefaultPOBook = -1")] in an immediate window it gives me the answer I am looking for if the Form [frmInpurForm] is open.

Hmmmmmmmmm

Look forward to any help at all
Rob

 

[ShaneMan]

Hey Rob,

Not sure that I got all the syntax right. All these double and single quotes make my head spin sometimes, but I'm taking it that UserName is text so you need to let DLookup know that and that DefaultPOBook is either a check box or a static value, and you need to let DLookup know that too. I saw that your thread had been sitting for awhile and thought I would TRY to help. I'm certainly not good at this but was willing to take a chance of being wrong, if it meant it might help ya or get ya going in the right direction.


cboPOBook.DefaultValue = DLookup("[UserPOBook]", "tblUsers", "[UserName] ='" & Forms![frmInputForm]!txtUsersInitials & "'" And "DefaultPOBook = -1")

HTH,
Shane

 

[Robert88]

Hi Shane,

Thanks for a reply.

OK, I sort of see the problem it would appear that I have a data type mismatch, hmmmm.

I tried your line and it is having trouble......

cboPOBook.DefaultValue = DLookup("[UserPOBook]", "tblUsers", "[UserName] ='" & Forms![frmInputForm]!txtUsersInitials & "'" And "DefaultPOBook = -1")

It gives me an error 13, type mismatch.

This info is from a table, tblUsers, it is looking for a "UserPOBook" which is a text field, with criteria "Username" (also a text field) equal to the users "txtUsersInitials", not sure what type this is as it is from the form (I do know it is a text box on the form) but along with it also equal to a "DefaultPOBook" = -1 in the tblUsers in order to get the users one and only default value (as only one is selected) when the DefaultPOBook is set to "YES", as it is a Yes/No field........

I suppose I am unsure of how to handle the txtUserInitials as it is from the form and maybe now looking at it together with the "YES/NO" field of DefaultPOBook, not sure what datatype the txtUsersInitials and the DefaultPOBook is to try and bring them so that they are all the same within the DLOOKUP?

Thanks Shane for your help so far, but thought I would paste this as I am new to all this and any support at the moment would be appreciated as not sure currently how to fix it. In the meantime I shall also play with the command a little in case I also get the result I am after.

Robert88

 

[ShaneMan]

Hey Robert,

Ok, lets try this:

cboPOBook.DefaultValue = DLookup("[UserPOBook]", "tblUsers", "[UserName] ='" & Forms![frmInputForm]!txtUsersInitials & "' And DefaultPOBook = -1")

If this doesn't work, try putting either 'Yes' or 'No' instead of -1 and see what it does. I warned you that I'm not all that good with syntax but I'm willing to try and help. Maybe someone will jump in if they see I'm advising wrong.

HTH,
Shane

 

[Robert88]

Hey cool,

Back to where I was when I tried

cboPOBook.DefaultValue = DLookup("[UserPOBook]", "tblUsers", "[UserName] = Forms![frmInputForm]!txtUsersInitials and DefaultPOBook = -1")

With your command above it now also works in an immediate window like this one above and also displays an #Name? in the combo box when the form opens............

It is an unbound combo box, should it be bound to something?

It seems that we now have two ways for the same result.......

I look forward to anyones input to get this working.

Robert88

 

[ShaneMan]

Hey Robert,

How many columns does cboPOBook have? If it has more than one column you may be referencing the wrong column. If you do not specify the column then Access assumes you mean the first column, cboPOBook.Column(0). If the column your trying to reference is the second column then it would need to be cboPOBook.Column(1). Access starts numbering it's columns at (0). Maybe your problem is there.

Also, try moving your code from the Default of cboPOBook to say the Current Event of the form and see if you get different results.

Lastly, I reread all your post and in your first post you give the SQL for the two different senerio's on cboPOBook. Maybe, through code, you could use those SQL's to change the recordsource of cboPOBook to accomplish what your trying to do.

HTH,
Shane

 

[Robert88]

Hi Shaneman,

Thanks for your suggestions, appreciate them.

The combo box has only one coloumn.

I have now tried your DLookup ststements in the default property of the combo box, with no luck.

I also tried encoding them into a Form_Load, no luck.

I then took your last suggestion and placed them in a Form_Current, with no luck either.

The reason for moving to DLookup was the SQL's were not successful.

I am think at the moment, maybe there is a better way of doing this. I am almost thinking of encoding the default values for each individual as a temporary solution. But this is not good from a user point of view as when staff change, the code is required to change rather than reference a table which a change user can change rather than code........... Not a good solution but got me also thinking maybe there is a better way?????

I think I am going to call lthis the COMBO BOX from HELL!!!!!!!

Look forward to any further suggestions if anybody has got any?

Robert88