Help with Two TextBox in one field and some PLEASE!!!

[cephal0n]

I have this peroblem thats really bugging me for days, please have a patience to read it and help me find the probplem because I knew I missed it and just cant tell where.
I have a table named tblProuctSummar y and it has the 6 fields:
ItemID - for unique key
BuyerA - for those who made the buying
BuyerB - for another buyer
Manufacturer -for identifying the source
Sold - for sold items = 1
Date - when it was sold

For problem:
1.I have to populate these fields using manual entry I used a form, so I added unbound control to my form
TextA - Entry for manufacturer
TextB - entry for manufacturer
CmdAdd - Add them to my table

Now that I have created them, I added a procedure in VB to add items, heres the code:

[CODE=vb]Public Sub AddRec()
Dim rsMyTable As Recordset

Set rsMyTable = New ADODB.Recordset
rsMyTable.Activ eConnection = CurrentProject. Connection
rsMyTable.Open "tblProductSumm ary", , adOpenKeyset, adLockOptimisti c, adCmdTable

'istart my adding
rsMyTable.AddNe w
Text0.SetFocus
rsMyTable.Field s("ItemID") = Text0.Text
Text2.SetFocus
rsMyTable.Field s("Manufacturer ") = Text2.Text
Text4.SetFocus
rsMyTable.Field s("Manufacturer ") = Text4.Text

rsMyTable.Updat e
End Sub[/CODE]

My problem is that when I assign two textbox for one field, why can’t msaccess accept this two entry, I’ve read some online tutorial, and some of them really make sure that by setting focus on the textbox, will automatically add the Items in the table.the reason I made two boxes in one field is becuase I want my table to show the date and how many Items were sold on that day. Why is this happening? Why does everytime I made an entry only one textbox is beeing recognise, I really wan them both to appear on my table.I really don’t understand.

And Now for problem 2: I need an Automatic insert Item for those Items that I have found similar and heres where the manufacturer field becomes populated, therefore I made another procedure using Insert Into in SQL, here’s the code and I added a check box for more combinations:
ChkA - for Sony
ChkB - for Sanyo
ChkC - for Philips

[CODE=vb]Public Sub InsertIntoTable ()
Dim cnn1 As ADODB.Connectio n
Dim rsMyTable As Recordset
Dim strCmd As String
Dim strCmd1 As String
Dim strCmd2 As String
Dim strCmd3 As String
Dim strCmd4 As String
Dim strCmd5 As String
Dim strCmd6 As String
Dim strCmd7 As String



Set cnn1 = New ADODB.Connectio n
cnn1.Provider = "Microsoft.Jet. OLEDB.4.0"
cnn1.Open "C:\Documen ts and Settings\Deskto p\db1.mdb”

strCmd = "INSERT INTO tblKPIsource(WH O])VALUES('Sony') "
strCmd1 = "INSERT INTO tblKPIsource([WHO])VALUES('Sanyo' )"
strCmd2 = "INSERT INTO tblKPIsource([WHO])VALUES('Philip s')"
strCmd3 = "INSERT INTO tblKPIsource([WHO])VALUES('Sony/Sanyo')"
strCmd4 = "INSERT INTO tblKPIsource([WHO])VALUES('Sony/Philips')"
strCmd5 = "INSERT INTO tblKPIsource([WHO])VALUES('Philip s/Sanyo')"
strCmd6 = "INSERT INTO tblKPIsource([WHO])VALUES('Sony/Philips/Sanyo')"

Set rsMyTable = New ADODB.Recordset
rsMyTable.Activ eConnection = CurrentProject. Connection
rsMyTable.Open "tblProductSumm ary", , adOpenKeyset, adLockOptimisti c, adCmdTable

If Me.chkA = True Then
cnn1.Execute strCmd
Else
If Me.chkB = True Then
cnn1.Execute strCmd1
Else
If Me.chkC = True Then
cnn1.Execute strCmd2
End If
End If
End If

‘combination of Items
If Me.chkA = True And Me.chkB = True Then
cnn1.Execute strCmd3
Else
If Me.chkA= True and Me.chkB= True Then
cnn1.Execute strCmd4
Else
If Me.chkB = True And Me.chkC= True Then
cnn1.Execute strCmd5
Else
If Me.chkB = True And Me.chkC= True And Me.chkB= True Then
cnn1.Execute strCmd6
End If
End If
End If
End If[/CODE]

What I want to achieve in my procedure is that after selecting only the single items, you can choose any combination. I don’t have any problem on my first set of my If-else with my checkbox, but the second half of my If-else really doesn’t participate very well. How come this happens when I put them precisely where I want them. Thinking about it shows that I really dont understand how to call check box when using it more than once.

And for problem 3, I noticed that when I call Insert Into query in my code, the table puts an empty value before it adds the item in the Insert Into sql.

why does this happend, why can’t access align itself to its other members, is there a secret code that must be done to make it align, why can’t access just follow what it must do!

Now there’s no more liquid left on my brain, for I have been racking my brains for a week now, drying it all up in purpose. Please help me, I’m dragging my mouse now for I have nowhere to go. In short I’m stuck, HELP pls!!!

[Scott Price]

Before we even get into your code, there are two problems I noticed right off with your table structure. First: You have two customer fields. Second: You are trying to enter double values into the manufacturer field. Both of these violate database design rules.

In the first case, you are limiting the number of customers you can associate with a particular order/manufacturer/transaction, etc... This may be fine now, while you can only think of a need to associate two, but what happens when your needs change in the future and you need to add another or three customers?

In the second case, multi-part entries into one field is just plain bad practice. It makes everything hard, as you are finding out :-)

Please take some time to read through Normalisation and Table structures and rethink the way your table is structured, leaving the coding problem alone for the moment. Then come back to the problem a little later and see what you think.

Proper design will make what you are trying to do quite simple, probably without using any code at all.

Regards,
Scott

[missinglinq]

Scott's advice is spot on, whereas

"I’ve read some online tutorial, and some of them really make sure that by setting focus on the textbox, will automatically add the Items in the table"

this advice is absolutely bogus! Code utilizing the .Text property does require setting focus to the control, but .Text shouldn't be used in this manner to begin with. When assigning values to a field/control you should use the .Value property, which doesn't require that focus be on the control.

rsMyTable.Field s("ItemID") = Text0.Text

should be

rsMyTable.Field s("ItemID") = Text0.Value

and .Valueis the default property of textboxes, can be shortened to rsMyTable.Field s("ItemID") = Text0

Linq ;0)>