Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Form That is something

Posted on 2014-01-28
8
Medium Priority
?
332 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

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 free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
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…

927 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