probably impossible but... combobox

[lala]

Hi, i have this problem that if the hugest pain for the past few months. i finally got it to work, finished the project, migrated to the sql server and now it's not working.
i'm assuming it's because the sql server has stricter rules???


anyhow, i need a combobox that's based on an id but displays the item name (so the id is the bound column but something else is displayed in the box).
and then i need for people to be able to also type whatever they want and for their choice to not show up in the dropdown.


the reasoning for that is that the dropdown only has items that are used most of the time but then once in a blue they want to be able to bill something on the fly as a one time thing.

so how do i make a combobox accept things and not display them in the dropdown??

 

[speakers_86]

Isn't that one of the reasons we use combo boxes? Just use the combo box wizard. Add the combo box, and use the wizard to add the required fields. There is a screen where you can set the column widths. At that screen you can hide any of the fields. If you search Youtube, you can probably find a video illustrating how to do this.

 

[nanscombe]

How about a ComboBox entry for "Other" and an extra field in the table where they can type whatever they want?

 

[lala]

i did already. i'm probably not explaining this right. hiding the fields is not a problem. my main problem is how to let the combobox accept any entry but NOT show them in the dropdown choices.

 

[lala]

How about an entry for "Other" and a extra field in the table to put it in?

yeah, that's my worse case scenario: using 2 fields. that's what i will do if i don't get any suggestions. i'm just making sure that there's not something there that i don't know before i do.

thank you))))))))))

 

[speakers_86]

Just set the limit to list property to no.

 

[lala]

Just set the limit to list property to no.

I can't when the bound column is hidden, it auto sets to YES

 

[speakers_86]

What if you don't hide it? You could try setting the width to .001, or as small as it will let you.

Or you could make an unbound combo box that uses dlookup to get the 'bound' value, and if dlookup is null it can just use whatever the user typed.

edit-What is this combo box bound to? If you hiding that, I would expect it to be an autonumber. It seems odd that you would consider an autonumber or free form text appropriate entries.

 

[lala]

What if you don't hide it? You could try setting the width to .001, or as small as it will let you.

this was an interesting idea)))))))))))))))) i went and tried but Access didn't like it, set it back to YES

Or you could make an unbound combo box that uses dlookup to get the 'bound' value, and if dlookup is null it can just use whatever the user typed.

and that's what i WAS doing with the access backend and it worked. once i switched to the sql server it stopped, here's the message
"Cannot add record(s); primary key of table 'SVCSERVICESETS' not in Recordset".

the SVCSERVICESETS table is the table where the values for the combo box are stored, so why it's a problem - i don't know: it's not linked to the table i'm in

i could've uploaded the db but unless you use sql server as a backend it works fine

edit-What is this combo box bound to? If you hiding that, I would expect it to be an autonumber. It seems odd that you would consider an autonumber or free form text appropriate entries.

it's bound to a number field in a table i'm trying to add the record to. it's called SERVICE and it's a number field.

and what do you mean about autonumber or free from being appropriate entries? what's wrong with using autonumber from another table? what would you use?

 

[missinglinq]

If the Bound Column is an Autonumber/Primary Key then Access is not going to allow you to enter a Value that doesn't already exist; you cannot edit/enter data into Autonumber Fields. You can:

• Go into Table Design and change the Bound Field to from an AutoNumber to a Number/Long Integer
• Create an Auto-Incrementing Routine to take over the job the AutoNumber has been doing (there's a gazillion examples here and elsewhere of this)

or

• Go the Textbox routine with the AfterUpdate event of the Combobox populating the Textbox.

This would also allow for the user to manually enter a number in the Textbox.

But to be honest, like speakers 86, I'm a little confused here! If the Bound Column is an AutoNumber/PK how can the user enter something in the Field that will mean anything?

Linq ;0)>

 

[speakers_86]

and that's what i WAS doing with the access backend and it worked. once i switched to the sql server it stopped, here's the message
"Cannot add record(s); primary key of table 'SVCSERVICESETS' not in Recordset".

It seems like if it works with the Access Be, then it should work with they SQL(?) back end too. It looks like your be is expecting a value from the combo box's source table, as if there is referential integrity going on. If there is, then of course you should do this. If not, then there is no issue. Is SvcServiceSets merely a list for the combo box, or is it some relevant table in your db? If it's just a list, you don't need a relationship drawn out. If that's the case, you can delete the relationship, and the error should go away. Maybe you accidentally created the relationship during the migration. Let me know if that helps.

and what do you mean about autonumber or free from being appropriate entries? what's wrong with using autonumber from another table? what would you use?

There is nothing wrong with using an autonumber as the bound value of your combo box. In fact that is what I usually do. I just though it was odd that you want to store either a Number or a String in the same field.

 

[lala]

i'm sorry, i'm not good at explaining things, i know that about myself, just bear with me please.


there's a table 'SVCSERVICESETS' that has ID which is an autonumber and SERVICE which is text.
there's another table FILESERVICES that has a SERVICE field which is a number. on the form the SERVICE field is a combobox with SERVICE as a bound field and after update it takes what the user chose or typed, looks it up in the 'SVCSERVICESETS' table and fills in the SERVICE number.

this was my way of achieving what i described above and what speakers (i think) also suggested.

once i moved to sql server this scenario doesn't work anymore

thank you for even trying with this

 

[lala]

It seems like if it works with the Access Be, then it should work with they SQL(?) back end too. It looks like your be is expecting a value from the combo box's source table, as if there is referential integrity going on. If there is, then of course you should do this. If not, then there is no issue. Is SvcServiceSets merely a list for the combo box, or is it some relevant table in your db? If it's just a list, you don't need a relationship drawn out. If that's the case, you can delete the relationship, and the error should go away. Maybe you accidentally created the relationship during the migration. Let me know if that helps.

i thought it should as well and was shocked when it didn't)))))
i don't have a relationship set up, if i go in the table itself and type the numbers - it takes everything fine.

read my setup above, i described exactly what's on the form. is it making more sense now?

omg, as i was typing this i realized what the problem is. my combobox is bound to the SERVICE field from SVCSERVICESETS table!!!!!!!!!!

i think that's the problem. but how do i solve it?

 

[lala]

here's the RowSource of the combobox

SELECT SVCSETSSERVICES.Service, SVCSETSSERVICES.ID 
FROM SVCSETSSERVICES WHERE (((SVCSETSSERVICES.ShowFile)=-1)) 
ORDER BY IIf([SortNo] Is Null,"NULL",[SortNo]);

and here's the RecordSource of the form

SELECT CLIENTFILESERVICES.*, 
SVCSETSSERVICES.Service AS EService, 
IIf(Not IsNull([invoicenumber]),
Val(Mid([InvoiceNumber],InStr([invoicenumber],"-")+1))) AS Expr1 
FROM SVCSETSSERVICES 
RIGHT JOIN CLIENTFILESERVICES ON 
SVCSETSSERVICES.ID = CLIENTFILESERVICES.Service 
ORDER BY IIf(Not IsNull([invoicenumber]),
Val(Mid([InvoiceNumber],InStr([invoicenumber],"-")+1))), IIf([SortNo] Is Null,"NULL",[sortno]);

 

[speakers_86]

i don't have a relationship set up, if i go in the table itself and type the numbers - it takes everything fine.

You typed NUMBERS. The error you are describing is when you type text. What is the datatype of the field that this combobox is bound to? It needs to be able to accept numbers and strings.

omg, as i was typing this i realized what the problem is. my combobox is bound to the SERVICE field from SVCSERVICESETS table!!!!!!!!!!

Since the field the combo box is bound to needs to accept numbers and strings, this mistake should not be raising this error.

i think that's the problem. but how do i solve it?

Just remake the combo box!

 

[lala]

Just remake the combo box!

for some reason you keep making me lol with your replies. i'm not sure what's funny but i thought i'd share)))))))))))


ok, it's not that easy, if i remake it - then it will stop doing what i wanted it to do. i'll be back to my problem of having a combo that should accept free text and not display it in the dropdown box.

You typed NUMBERS. The error you are describing is when you type text. What is the datatype of the field that this combobox is bound to? It needs to be able to accept numbers and strings.

i always setup my comboboxes this way, is this wrong? i create a table with ID (autonumber), FieldName (text).
then in all tables that use these entries i setup a field (NUMBER) that relates to the autoid field of the table with the text entries.

that way i only store numbers in the tables and the relating text is in a different table just once.

is this wrong?

 

[lala]

If the Bound Column is an Autonumber/Primary Key then Access is not going to allow you to enter a Value that doesn't already exist; you cannot edit/enter data into Autonumber Fields. You can:

• Go into Table Design and change the Bound Field to from an AutoNumber to a Number/Long Integer
• Create an Auto-Incrementing Routine to take over the job the AutoNumber has been doing (there's a gazillion examples here and elsewhere of this)

or

• Go the Textbox routine with the AfterUpdate event of the Combobox populating the Textbox.

This would also allow for the user to manually enter a number in the Textbox.

But to be honest, like speakers 86, I'm a little confused here! If the Bound Column is an AutoNumber/PK how can the user enter something in the Field that will mean anything?

Linq ;0)>

hey, the reply right below speakers' was for you, i forgot to quote you))))

 

[nanscombe]

This is the sort of thing I meant when I said about adding an "Other" option plus textbox.

 

[lala]

This is the sort of thing I meant when I said about adding an "Other" option plus textbox.

thank you. that's what i'm doing as i'm talking to you guys. i came here hoping that i'm missing something and that i won't have to have it this way.

there's a few downsides to it (for my purposes). and also, this database is huge, i was hoping to launch it on tuesday and this is a major change, a lot depends on this field all around the db.

but unfortunately it seems like this is my only option


thank you again

 

[nanscombe]

I take it there's no comment field on the record that could be co-opted, i.e. a "See comments" option instead of an "Other"?