allow entering empty or null field

[abhishek chauhan]

I am developing a software on inventory with ms-access 2003 as backend and vb 6.0 as front end. There are two tables named Supplier(SupplierID, Name, Address, Phone NO) and Item(ItemCode, ItemName, SupplierID, Category, Price). SupplierID is primary Key in Supplier table and foreign key in Item table. I have established relationship between these two tabled and enforced referential integrity.
But SupplierID is optional in Item table. and its properties Required is set to NO and allow zero length is set to yes in both the tables.
when i try to leave the SupplerId empty or set it to null using vb code , while entering information in ItemForm
an error occured "you cannot add or change a record because a related record is required in table Supplier"

I require that a user may fill the information about supplier in item form if he
wishes but not made it compulsory
How can i solve this problem.

I need an urgent help. Please................

[Delerna]

What is the relationship type between Item.SupplierID and Supplier.SupplierID

[abhishek chauhan]

What is the relationship type between Item.SupplierID and Supplier.SupplierID

Thanks Delerna

Relationship type is one-to many.

[Delerna]

OK thats what it should be.
Hmm, I have tried it in a mock up and I don't have any problems.
Are you sure the probem is caused by the SupplierID, is it possible that a relationshipon between the supplier table and some other table is causing it?

Also is the one to many the correct way around?

[abhishek chauhan]

OK thats what it should be.
Hmm, I have tried it in a mock up and I don't have any problems.
Are you sure the probem is caused by the SupplierID, is it possible that a relationshipon between the supplier table and some other table is causing it?

Also is the one to many the correct way around?

I am a BCA student and new to VB 6.0, i think there is something wrong in my coding.
I tried to enter the record In Item table directly in database without using VB 6.0 forms. I left the SupplierID field empty. It worked well. The problem occurs only while entering information in Item Form using vb 6.0 interface. i have pasted the code for save button on ITEM FORM below:

If Text1.Text = "" Then
MsgBox "Please Enter Item Code", vbInformation, "Item Code"
Text1.SetFocus
Else

If Text2.Text = "" Then
MsgBox "Please Enter Item Name", vbInformation, "ItemName"
Text2.SetFocus
Else

If DataCombo1.Text = "" Then
MsgBox "Please Select Category", vbInformation, "Category"
DataCombo1.SetFocus
Else

If DataCombo2.Text = "" Then
MsgBox "Please Select Size", vbInformation, "Size"
DataCombo2.SetFocus
Else

If DataCombo3.Text = "" Then
MsgBox "Please Select Unit", vbInformation, "Unit"
DataCombo3.SetFocus
Else

con.Open " Provider = Microsoft.Jet.OLEDB.4.0;Data Source = " & App.Path & "\IS.mdb"
s = "SELECT ItemCode FROM Item WHERE ItemCode = '" & Text1.Text & "'"
Set rs = con.Execute(s)
If rs.EOF = True Then
con.Execute "INSERT INTO Item(ItemCode, Dt_of_insertion,ItemName, PartyID, CategoryID, SizeID, UnitID, SalePrice, RatePerUnit, Stock, MaximumQty, MinimumQty, Reorder)values('" & Text1.Text & "', '" & DTPicker1.Value & "', '" & Text2.Text & "', '" & Text10.Text & "', '" & DataCombo1.BoundText & "', '" & DataCombo2.BoundText & "', '" & DataCombo3.BoundText & "', '" & Text4.Text & "', '" & Text3.Text & "', '" & Text5.Text & "', '" & Text6.Text & "', '" & Text7.Text & "', '" & Text8.Text & "')"
MsgBox "Item Added", vbInformation, "New Item"
con.Close

Text1.SetFocus
Else
MsgBox "This Item Code Exists Already. Enter Different Code", vbInformation, "Item Code"
Text1.Text = ""
Text1.SetFocus
con.Close

End If
End If
End If
End If
End If
End If

End Sub

TEXT10 CONTAINS THE SupplierID of selected supplier.

[Delerna]

Try temporarily removing Text10.Text from the query
so that this bit

DTPicker1.Value & "', '" & Text2.Text & "', '" & Text10.Text & "', '" &
 

becomes this

DTPicker1.Value & "', '" & Text2.Text & "', null, '" &
 

Im just thinking that Text10.value might be returning some spurious value such as a space ???

If doing the above resolves the problem, then that would be the problem and you will need to come up with a way to stop it

[abhishek chauhan]

Try temporarily removing Text10.Text from the query
so that this bit

DTPicker1.Value & "', '" & Text2.Text & "', '" & Text10.Text & "', '" &
 

becomes this

DTPicker1.Value & "', '" & Text2.Text & "', null, '" &
 

Im just thinking that Text10.value might be returning some spurious value such as a space ???

If doing the above resolves the problem, then that would be the problem and you will need to come up with a way to stop it

I replaced text10.text with null without quotes and that solves the problem.

I tried the following way to get rid of this problem.
I took a variable as variant and stored value of text10.text in that variable.
and assign it to Null if text10.text is empty.
and then replaced text10.text with that variable and tried storing that value in database.
but it again lead to the same error. Code is shown below

con.Open " Provider = Microsoft.Jet.OLEDB.4.0;Data Source = " & App.Path & "\IS.mdb"
s = "SELECT ItemCode FROM Item WHERE ItemCode = '" & Text1.Text & "'"
Set rs = con.Execute(s)
If rs.EOF = True Then
If Text10.Text = "" Then
temp = Null
Else
temp = Text10.Text
End If

con.Execute "INSERT INTO Item(ItemCode, Dt_of_insertion,ItemName, PartyID, CategoryID, SizeID, UnitID, SalePrice, RatePerUnit, Stock, MaximumQty, MinimumQty, Reorder)values('" & Text1.Text & "', '" & DTPicker1.Value & "', '" & Text2.Text & "','" & temp & "', '" & DataCombo1.BoundText & "', '" & DataCombo2.BoundText & "', '" & DataCombo3.BoundText & "', '" & Text4.Text & "', '" & Text3.Text & "', '" & Text5.Text & "', '" & Text6.Text & "', '" & Text7.Text & "', '" & Text8.Text & "')"
MsgBox "Item Added", vbInformation, "New Item"
con.Close
emp
Text1.SetFocus
Else
MsgBox "This Item Code Exists Already. Enter Different Code", vbInformation, "Item Code"
Text1.Text = ""
Text1.SetFocus
con.Close

End If
End If
End If
End If
End If
End If

End Sub

[Delerna]

OK good thinking but
if text10.text is returning a spurious value then

If Text10.Text="" won't work because Text10.Text won't equal ""


try
msgbox "##" & Text10.Text & "##"

in your code so you can see if there is a value like a space in Text10.Text

[abhishek chauhan]

OK good thinking but
if text10.text is returning a spurious value then

If Text10.Text="" won't work because Text10.Text won't equal ""


try
msgbox "##" & Text10.Text & "##"

in your code so you can see if there is a value like a space in Text10.Text

text10.text ="" checks that if text10 is empty or user has not entered anything in it.
i tried above code suggested by you
and message box just displayed #### without any space or some other value

[Delerna]

Hmm. Try this

Dim strSQL as String
 
If rs.EOF = True Then
 
If Text10.Text = "" Then
strSQL="INSERT INTO Item(ItemCode, Dt_of_insertion,ItemName, PartyID, CategoryID, SizeID, UnitID, SalePrice, RatePerUnit, Stock, MaximumQty, MinimumQty, Reorder)values('" & Text1.Text & "', '" & DTPicker1.Value & "', '" & Text2.Text & "',null, '" & DataCombo1.BoundText & "', '" & DataCombo2.BoundText & "', '" & DataCombo3.BoundText & "', '" & Text4.Text & "', '" & Text3.Text & "', '" & Text5.Text & "', '" & Text6.Text & "', '" & Text7.Text & "', '" & Text8.Text & "')"
Else
strSQL="INSERT INTO Item(ItemCode, Dt_of_insertion,ItemName, PartyID, CategoryID, SizeID, UnitID, SalePrice, RatePerUnit, Stock, MaximumQty, MinimumQty, Reorder)values('" & Text1.Text & "', '" & DTPicker1.Value & "', '" & Text2.Text & "','" & Text10.Text & "', '" & DataCombo1.BoundText & "', '" & DataCombo2.BoundText & "', '" & DataCombo3.BoundText & "', '" & Text4.Text & "', '" & Text3.Text & "', '" & Text5.Text & "', '" & Text6.Text & "', '" & Text7.Text & "', '" & Text8.Text & "')"
End If
 
con.Execute strSQL
 
MsgBox "Item Added", vbInformation, "New Item"
con.Close
emp
Text1.SetFocus
Else
MsgBox "This Item Code Exists Already. Enter Different Code", vbInformation, "Item Code"
Text1.Text = ""
Text1.SetFocus
con.Close
 
End If
 
 

[abhishek chauhan]

Hmm. Try this

Dim strSQL as String
 
If rs.EOF = True Then
 
If Text10.Text = "" Then
strSQL="INSERT INTO Item(ItemCode, Dt_of_insertion,ItemName, PartyID, CategoryID, SizeID, UnitID, SalePrice, RatePerUnit, Stock, MaximumQty, MinimumQty, Reorder)values('" & Text1.Text & "', '" & DTPicker1.Value & "', '" & Text2.Text & "',null, '" & DataCombo1.BoundText & "', '" & DataCombo2.BoundText & "', '" & DataCombo3.BoundText & "', '" & Text4.Text & "', '" & Text3.Text & "', '" & Text5.Text & "', '" & Text6.Text & "', '" & Text7.Text & "', '" & Text8.Text & "')"
Else
strSQL="INSERT INTO Item(ItemCode, Dt_of_insertion,ItemName, PartyID, CategoryID, SizeID, UnitID, SalePrice, RatePerUnit, Stock, MaximumQty, MinimumQty, Reorder)values('" & Text1.Text & "', '" & DTPicker1.Value & "', '" & Text2.Text & "','" & Text10.Text & "', '" & DataCombo1.BoundText & "', '" & DataCombo2.BoundText & "', '" & DataCombo3.BoundText & "', '" & Text4.Text & "', '" & Text3.Text & "', '" & Text5.Text & "', '" & Text6.Text & "', '" & Text7.Text & "', '" & Text8.Text & "')"
End If
 
con.Execute strSQL
 
MsgBox "Item Added", vbInformation, "New Item"
con.Close
emp
Text1.SetFocus
Else
MsgBox "This Item Code Exists Already. Enter Different Code", vbInformation, "Item Code"
Text1.Text = ""
Text1.SetFocus
con.Close
 
End If
 
 

Thanks a lot.
It has solved the problem.
It was so easy but it took me about 8 hours to get rid of this.
you have saved my life.

[Delerna]

You are very welcome and they usually are very easy when you look back