?
Solved

Form That is something

Posted on 2014-01-28
8
Medium Priority
?
333 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 49

Accepted Solution

by:
Dale Fye earned 2000 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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

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 49

Expert Comment

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

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

If Skype for Business came with your office 2016 or office 365 installation, you may find that it's almost impossible to either disable or remove it. The application will often launch with each start of Windows, even when explicitly configured not t…
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

588 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