Update table from form using vba

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

We have a form which nees to update a table however I need to complete the
coding in VBA but everything I enter does not work. Not only does the table
need to be populated using text and combo boxes but I need to do a serach
before adding the data into the table as we have a few combo boxes with info
that needs to be added under the same feild in the table.
example: 2 combo boxes called melb and syd. melb has numbers 9324, 9334,
9344 while syd has 9201, 9206, 9251. From melb comobox we select 9324 and syd
we select 9201 then we need to records to be entered into the table with the
same details from all the other text boxes etc but the table field called
state will need to show the 9324 as one record and 9201 as the next record.

I would greatlly appreciate any help as I have tried all different versions
for the past 2 weeks.
 
I did not quite understand what kind of search you need to
perform, but if you need to just put data in the table,
then you can use either DAO or ADO library to accomplish
the task. For example, by using DAO:

Private Sub myButton_Click()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("myTable")
' if you need to add new record
rs.AddNew
rs!Field1 = txtField1
...
rs!FieldN = txtFieldN
rs!State = cmbMelb
rs.Update
' Now update Sydney
rs.AddNew
rs!Field1 = txtField1
...
rs!FieldN = txtFieldN
rs!State = cmbSydn
rs.Update
' Close the recordset
rs.Close
End Sub
 
Hi Sergey
Thank you for your help.
regarding my search.
Lets say we have three brands of tea and each brand has 3 different
flavours. We have the ability to chose one flavour from each brand of tea to
place an order.
Each order requires an order number but the order numbers differ based on a
number given to each flavour.
In our table we want to have in column that shows the flavour of the tea but
before adding the order we need to search for the last order placed for the
flavours so the new order numbers will be the next sequence.
ie
Following are 3 brands with 3 flavours
(Combobox) (Selections available)
Lipton = Rasberry (931), Lemon(932), Chamomile(933)
Twinings = Cranberry(921), quiet Chamomile(922), Strawberry(923)
Dilmah = Earl Grey(941), Peppermint(942), Orange(943)
We decide to choose:
Lipton = Rasberry(which = 931)
Twinings = Strawberry(which = 923)
The table will show:
Order No Flavour
9310001 Rasberry
9230001 Strawberry
9410001 Earl Grey
new order inserted should be after checking current details in table:
Order No Flavour
9310002 Rasberry
9230002 Strawberry
Add when we do another order before updating the table a search will be
completed to find the last order number for specific Flavour and then add a 1
to the order number:
ie
Rasberry = new order number would be 9310003

I hope this explains my query.
Thank you in advance for your help
 
If you need a separate order numbers for different brands
then separate them (numbers) into two fields:

Order table:
BrandID (for example, 931, 932, etc.)
OrderNo (for example, 001, 002, etc.)

and then, on the report (or form) just concatenate them
together: Make the control that shows the number readonly
(label, for example) and set it to =[BrandID] & OrderNo.

And if you have to pick the next available number:

NextNo = DMax("OrderNo", "Orders", "BrandID =" & 932)

HTH
 
Back
Top