Solved

Form That is something

Posted on 2014-01-28
8
323 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
 

Author Comment

by:Rayne
ID: 39815862
am i missing anything
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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 viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
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 …

914 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now