Combo Box & Null Value (1 Viewer)

[IpeXeuS]

Hello there,

I've one question to make about combo box and null values. Ok, is it possible to let's say set combo box value to null some how? I've tryied to do something with this but I get error message 3058 (Index or primary key cannot contain a Null value) everytime. That combo box is not primary key, but it's indexed, so there can't be same values in that table.

Let me explain a bit more. Ok, I've one combo box which rowsource type is table/query and rowsource is connected to table with ID. I've also checkbox there. What I try to do here is that when I check that box, system will automatically set that combo box value to null, but I can't do this cause I get that error message 3058 (Index or primary key cannot contain a Null value).

Private Sub Card_Returned_AfterUpdate()
On Error GoTo Err_Card_Returned_AfterUpdate

    If (Me![Card_Returned].Value = -1) Then
        [Forms]![frm_Details]![frm_Details_Sub_Table]![Card_Return_Date] = Now
        Me![Card_Return_Date].Requery
        Me![Card].Value = ""
        Me![Card].Requery
    Else
        [Forms]![frm_Details]![frm_Details_Sub_Table]![Card_Return_Date] = ""
        Me![Card_Return_Date].Requery
    End If
    
Exit_Card_Returned_AfterUpdate:
    Exit Sub
Err_Card_Returned_AfterUpdate:
    Exit Sub
End Sub

All advices etc. are approciated!

Thanks!

 

[Bat17]

if it is a text data type you could use an empty string ""

Peter

 

[IpeXeuS]

Ok and what about if it's a number data type?

 

[IpeXeuS]

Let me explain it simple enough. How can the value of a combo box be changed to blank or null? If a user selects a value from a combo box and then how can it be changed back to nothing?

 

[RuralGuy]

If the ComboBox is bound to a numeric field then you can *not* set it back to a Null! If the record has not been saved then the best you can do is Me.ComboBoxName.UnDo.

 

[IpeXeuS]

Thanks to you for your advice mate, approciate. Btw I noticed that when I add some brand new records, then I can set that combo box value to null, but when I close database, I can't do that anymore cause records has been saved. Maybe I just test same thing with text data type or does anyone have any other suggestions?

 

[IpeXeuS]

I tryied with text data type but I get same error 3058 as well. Hmm, I've another database where's almost same thing but it doesn't work in this db, weird...

 

[RuralGuy]

You may have to go with the Text field to get the results you need. It can *not* be set to a Null either but you can set it to a ZeroLengthString (ZLS = "")

 

[boblarson]

Now, when you say you want to set it to null (or a zero length string), it would seem to me that your primary trouble is that you are trying to say to a table, act as if I never gave you data to store.

My question is, why do you have a control bound to a field and then want to clear it? Wouldn't you want to keep your data in your record, or maybe I'm not understanding what you really want to accomplish. If you are using the control to look something up, it shouldn't be bound.

 

[IpeXeuS]

Indeed, I check out that Required Property is set to no and AllowZeroLength Property is set to yes in table field properties, but I get still that same error message 3058 (Index or primary key cannot contain a Null value), weird indeed.

 

[RuralGuy]

Are you changing the Primary Key field? That is not a particularly good thing to do.

 

[IpeXeuS]

I don't think so mate, what I try to change here is that value in another table and it's linked with rowsource to this combo box with ID.

But, let me specify what I try to complish here a bit more to you guys. Ok, first of all I'm trying to do some sort of visitor card database. Card numbers is in that combo box and there's certain amount available of those cards. They're unique so one card can not be in two records and also that detail in combo box stays there a while (we're speaking days or weeks), then it'll be released (usually when visiting is over) to new visitors.
Here's tables and fields:

1. Table: tbl_Visitor_Data

tbl_Visitor_ID
tbl_Visitor_Card_ID

(SELECT DISTINCTROW tbl_Visitor_Card.Visitor_Card_ID, tbl_Visitor_Card.Visitor_Card_Data FROM tbl_Visitor_Card ORDER BY tbl_Visitor_Card.Visitor_Card_Data)

tbl_Visitor_Firstnames
tbl_Visitor_Lastname
tbl_Visitor_Card_Granted
tbl_Visitor_Card_Grant_Date
tbl_Visitor_Card_Returned
tbl_Visitor_Card_Return_Date
tbl_Visitor_Remarks

2. Table: tbl_Card_Data

tbl_Card_ID
tbl_Card_Data (number of card)
tbl_Card_Remarks

I hope this brief help you out to figure out what I try to complish here and if anyone have any other solution to complish this, please share that with me, I would be very grateful indeed.

 

[IpeXeuS]

Heres a updated information to you. I found out why I get that error message 3058. It's because table field index was set to Yes (No Dublicates) and when I change it to Yes (Dublicates OK) everythings OK.

But now there's another thing, now user can add duplicate card number to system, but it would be great if user can't add duplicate card number to system, cause there can't be same visitor card used at the same time by two different visitors, so any suggetions?

 

[RuralGuy]

Looking at this thread should help.

 

[IpeXeuS]

I read it and it was very helpful thread, now all seems to work perfectly, thanks to you.

 

[RuralGuy]

Great! Thanks for posting back with your success.