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 tblProuctSummary 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:

Public Sub AddRec()
Dim rsMyTable As Recordset
 
Set rsMyTable = New ADODB.Recordset
rsMyTable.ActiveConnection = CurrentProject.Connection
rsMyTable.Open "tblProductSummary", , adOpenKeyset, adLockOptimistic, adCmdTable
 
'istart my adding
rsMyTable.AddNew
Text0.SetFocus
rsMyTable.Fields("ItemID") = Text0.Text
Text2.SetFocus
rsMyTable.Fields("Manufacturer") = Text2.Text
Text4.SetFocus
rsMyTable.Fields("Manufacturer") = Text4.Text
 
rsMyTable.Update
End Sub

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

Public Sub InsertIntoTable()
Dim cnn1 As ADODB.Connection
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.Connection
cnn1.Provider = "Microsoft.Jet.OLEDB.4.0"
cnn1.Open "C:\Documents and Settings\Desktop\db1.mdb”
 
strCmd = "INSERT INTO tblKPIsource(WHO])VALUES('Sony')"
strCmd1 = "INSERT INTO tblKPIsource([WHO])VALUES('Sanyo')"
strCmd2 = "INSERT INTO tblKPIsource([WHO])VALUES('Philips')"
strCmd3 = "INSERT INTO tblKPIsource([WHO])VALUES('Sony/Sanyo')"
strCmd4 = "INSERT INTO tblKPIsource([WHO])VALUES('Sony/Philips')"
strCmd5 = "INSERT INTO tblKPIsource([WHO])VALUES('Philips/Sanyo')"
strCmd6 = "INSERT INTO tblKPIsource([WHO])VALUES('Sony/Philips/Sanyo')"
 
Set rsMyTable = New ADODB.Recordset
rsMyTable.ActiveConnection = CurrentProject.Connection
rsMyTable.Open "tblProductSummary", , adOpenKeyset, adLockOptimistic, 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

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.Fields("ItemID") = Text0.Text

should be

rsMyTable.Fields("ItemID") = Text0.Value

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

Linq ;0)>