Solved

Form That is something

Posted on 2014-01-28
8
324 Views
Last Modified: 2014-01-28
Hello All,

To all VBA Gurus and Access Form Experts,

There is  a table that user need to fill up using a a front end form.
Example: When user selected a value for icode,, cntryCode and pipeline(however many), on hitting the button “add selection” – the macro adds new rows to the table. At that point – users can just type in the Values for cat1Cost,cat2Cost and cat3Cost in a datasheet view within the same form.

For example >> (1) when I select >>
cntryCode = “CN”
icode = “11”
Pipeline >> “D201” , “D301”
See attached “before please” for how the form might look at that point before the button is pressed

(2) then press the button “Add Selections”

(3) Two rows get added to the [Item_Country_DisPipe_LINE] table and also show up in the subForm - frmItem_Country_DisPipe_LINE
See attached “afterplease” for how the form will look after pressing the button and then typing in the price  for the cat1Cost,cat2Cost,cat3Cost


Thank you
howDoesThisLook2014-Working.accdb
BeforePlease.png
afterPLEASE.png
0
Comment
Question by:Rayne
  • 6
  • 2
8 Comments
 

Author Comment

by:Rayne
ID: 39815799
How can I program the sub form to capture the selections and create the empty rows for the users to fill their values for cat1Cost, catr2Cost and cat3Cost?
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 39815826
Well, if you've gotten to the point where the listbox on the right contains those two values.  Then it the code behind the cmd_Add button would look something like:

Private Sub cmd_Add_Selection_Click

    Dim strSQL as string
    Dim intLoop as integer

    'assuming that your cntryCodes are strings and iCodes are numeric
    For intLoop = 0 to me.lst_SelectedPipelines.count - 1
        strSQL = "INSERT INTO yourTable (iCode, cntryCode, dpCode) " _
                    & "Values (" & me.cbo_iCode & ", '" & me.cbo_cntryCode & "', " _
                                         & "'" & me.lst_SelectedPipelines & "')"
        currentdb.execute strsql, dbfailonerror
    Next

    me.SubformControName.Requery

End Sub

Open in new window

0
 

Author Comment

by:Rayne
ID: 39815861
Hello Dale,

I tried your code. I renamed it to fit my file. Its not working….

Private Sub Command14_Click()
On Error GoTo g
    Stop
    Dim strSQL As String
    Dim intLoop As Integer
    Dim ItemIndex

    'assuming that your cntryCodes are strings and iCodes are numeric
    'For intLoop = 0 To Me.lst_SelectedPipelines.Count - 1
    For Each ItemIndex In Me.lbxRight.ItemsSelected
        strSQL = "INSERT INTO yourTable (iCode, cntryCode, dpCode) " _
               & "Values (" & Me.cboItem & ", '" & Me.cboCountry & "', " _
               & "'" & Me.lbxLeft.ItemData(ItemIndex) & "')"
        Debug.Print strSQL
        CurrentDb.Execute strSQL, dbFailOnError
    Next

   '' Me.SubformControName.Requery
   
g:
    Debug.Print Err.Number & "__" & Err.Description
End Sub
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:Rayne
ID: 39815862
am i missing anything
0
 

Author Comment

by:Rayne
ID: 39815911
Never mind Dale, I think I am getting to it :)
Thank you for your priceless help, greatly appreicted
0
 

Author Closing Comment

by:Rayne
ID: 39815912
Awesome Guy
0
 

Author Comment

by:Rayne
ID: 39815928
Thank you Sir, thank you
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39816197
Sorry I wasn't able to re-engage.  Glad you were able to get it to work.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question