467,918 Members | 1,254 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,918 developers. It's quick & easy.

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

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:

Expand|Select|Wrap|Line Numbers
  1. Public Sub AddRec()
  2. Dim rsMyTable As Recordset
  4. Set rsMyTable = New ADODB.Recordset
  5. rsMyTable.ActiveConnection = CurrentProject.Connection
  6. rsMyTable.Open "tblProductSummary", , adOpenKeyset, adLockOptimistic, adCmdTable
  8. 'istart my adding
  9. rsMyTable.AddNew
  10. Text0.SetFocus
  11. rsMyTable.Fields("ItemID") = Text0.Text
  12. Text2.SetFocus
  13. rsMyTable.Fields("Manufacturer") = Text2.Text
  14. Text4.SetFocus
  15. rsMyTable.Fields("Manufacturer") = Text4.Text
  17. rsMyTable.Update
  18. 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

Expand|Select|Wrap|Line Numbers
  1. Public Sub InsertIntoTable()
  2. Dim cnn1 As ADODB.Connection
  3. Dim rsMyTable As Recordset
  4. Dim strCmd As String
  5. Dim strCmd1 As String
  6. Dim strCmd2 As String
  7. Dim strCmd3 As String
  8. Dim strCmd4 As String
  9. Dim strCmd5 As String
  10. Dim strCmd6 As String
  11. Dim strCmd7 As String
  15. Set cnn1 = New ADODB.Connection
  16. cnn1.Provider = "Microsoft.Jet.OLEDB.4.0"
  17. cnn1.Open "C:\Documents and Settings\Desktop\db1.mdbĒ
  19. strCmd = "INSERT INTO tblKPIsource(WHO])VALUES('Sony')"
  20. strCmd1 = "INSERT INTO tblKPIsource([WHO])VALUES('Sanyo')"
  21. strCmd2 = "INSERT INTO tblKPIsource([WHO])VALUES('Philips')"
  22. strCmd3 = "INSERT INTO tblKPIsource([WHO])VALUES('Sony/Sanyo')"
  23. strCmd4 = "INSERT INTO tblKPIsource([WHO])VALUES('Sony/Philips')"
  24. strCmd5 = "INSERT INTO tblKPIsource([WHO])VALUES('Philips/Sanyo')"
  25. strCmd6 = "INSERT INTO tblKPIsource([WHO])VALUES('Sony/Philips/Sanyo')"
  27. Set rsMyTable = New ADODB.Recordset
  28. rsMyTable.ActiveConnection = CurrentProject.Connection
  29. rsMyTable.Open "tblProductSummary", , adOpenKeyset, adLockOptimistic, adCmdTable
  31.     If Me.chkA = True Then
  32.         cnn1.Execute strCmd
  33.     Else
  34.         If Me.chkB = True Then
  35.             cnn1.Execute strCmd1
  36.                 Else
  37.                     If Me.chkC = True Then
  38.                         cnn1.Execute strCmd2
  39.        End If
  40.     End If
  41. End If
  43.     Ďcombination of Items    
  44.         If Me.chkA = True  And  Me.chkB = True Then
  45.         cnn1.Execute strCmd3
  46.     Else
  47.         If Me.chkA= True and Me.chkB= True Then
  48.             cnn1.Execute strCmd4
  49.                 Else
  50.                     If Me.chkB = True And Me.chkC= True Then
  51.                         cnn1.Execute strCmd5
  52.         Else
  53.         If Me.chkB = True And Me.chkC= True And Me.chkB= True Then    
  54.         cnn1.Execute strCmd6
  55.     End If
  56.        End If
  57.     End If
  58. 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!!!
Mar 15 '08 #1
  • viewed: 1983
2 Replies
Scott Price
Expert 1GB
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.

Mar 15 '08 #2
Expert 2GB
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)>
Mar 15 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Leon Shaw | last post: by
3 posts views Thread by Julia | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.