Solved

Form That is something

Posted on 2014-01-28
8
328 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

751 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