box showing choices, select multiple choices for each record (1 Viewer)

[gcoaster]

Hello

Hi Adam, Learning HEAPS.

Yes, I tried everything
I tried everything in this little article i found last night.

SUBFORMS

Referencing subform controls always throws Access users for a loop while they’re new to using Visual Basic for Applications (VBA). That’s because Access views the subform as just another one of the form’s controls.

To refer to a form, identify the form via the Forms collection as follows:

Forms!formname

Reference specific controls and subforms via the form’s Controls collection:

Forms!formname!subformname

The subform is actually a control, but it’s a control with dependent controls. That means you must identify both levels: the subform and the subform’s controls. To do so, use the subform (control’s) Form property in the form:

Forms!formname!subformname.Form.controlname

If the object name contains spaces, you must enclose any instance in brackets:
Forms![form name]![control name].Form.[control name]

In addition, you can use the Me identifier when the form is current, as follows:
Me!subformname.Form.controlname

 

[gcoaster]

Its Working

ADAM! I got it working!

One interesting thing.. every time i renamed machineID in .AddNew section
!machineFK = Forms!frmMACHINE!MachineID
it kept changing the small case m in machineID to large case M
so i did some renaming. and then BAM!

I learned a couple of valuable things, to help with field locations in subforms, use the ..Builder. and before i had for instance a table named MACHINE and then a form named MACHINE, that made things REALLY confusing.



Here is the entire Code..

Option Compare Database

Private Sub cmdinstall_Click()

  If IsNull(Me.lstInstalled) And Not IsNull(Me.lstSoftware) Then

    'Dim MyMessage
    'MyMessage = MsgBox("This will add a new installation to this record.  Continue?", vbYesNoCancel)
  'If MyMessage = vbYes Then

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone

  With rs
  
    .AddNew
    
    !client = Forms!frmMACHINE!clientFK
    !machineFK = Forms!frmMACHINE!tblMachineID
    !title = Me.lstSoftware
    
    .Update
    
  End With

    Me.lstInstalled.Requery
    Me.lstSoftware.Requery
  
    Me.lstInstalled = Null
    Me.lstSoftware = Null

Set rs = Nothing
  
  End If
  
    'Else: MsgBox "Please select only a software to install", vbOKOnly
  'End If

End Sub

Private Sub cmduninstall_Click()

  If IsNull(Me.lstSoftware) And Not IsNull(Me.lstInstalled) Then

    'Dim MyMessage
    'MyMessage = MsgBox("This will delete the selected software's record from this machine.  Continue?", vbYesNoCancel)
  'If MyMessage = vbYes Then
  
    DoCmd.SetWarnings False
     DoCmd.RunSQL "DELETE * FROM tblMACHINEsoft WHERE tblMACHINEsoft.client = Forms!frmMACHINE!clientFK AND " & _
                 "tblMACHINESOFT.machineFK = Forms!frmMACHINE!tblMachineID AND " & _
                 "tblMACHINEsoft.title = Forms!frmMACHINE!frmMACHINEsoft.Form!lstInstalled"

    Me.lstInstalled.Requery
    Me.lstSoftware.Requery
    
    Me.lstInstalled = Null
    Me.lstSoftware = Null
  
  End If
   'Else: MsgBox "Please select only a software to Uninstall", vbOKOnly
  'End If

End Sub

Private Sub Form_Current()

  Me.lstInstalled.RowSource = "SELECT tblMACHINESOFT.title FROM tblMACHINESOFT " & _
                              "WHERE tblMACHINESOFT.client = forms!frmMACHINE!cboClientFK"
                              
Me.lstSoftware.RowSource = "SELECT tblSOFTWARE.title FROM tblSOFTWARE " & _
                            "WHERE tblSOFTWARE.title NOT IN " & _
                            "(SELECT tblMACHINESOFT.title FROM tblMACHINESOFT " & _
                            "WHERE tblMACHINESOFT.client = forms!frmMACHINE!cboClientFK)"

End Sub

 

[ajetrumpet]

before i had for instance a table named MACHINE and then a form named MACHINE, that made things REALLY confusing.

I'm sure it did. I think the common fix to avoid running into that is using name prefixes like "tbl", "qry", "frm", etc... I see a lot of objects with those strings in front of the names. Anyway, your code looks perfect!! Congrats!!

And if you want to know something interesting about subforms too (more confusing than interesting), read this...

"Subforms are actual controls just like text boxes and command buttons. If you drag and drop a form onto another, that dropped form is NOT a subform, by definition, and therefore will not cooperate with you if you are trying to use standard subform syntax to reference it. It must be treated like a stand-alone object."Believe or not, there are people that still don't believe this, and constantly post questions about these aspects. What you read looks like one of the GOOD articles on subforms. Make sure to hang on to that one!

 

[gcoaster]

Thank you Adam,
Yea, with subforms its tricky. I WILL Make sure to hang on to that one!

I am now applying your listbox solution to other areas of my database and I am stuck again!

Question:

I figured out how to get more then just one column into listbox,

then... How do I INSERT more then one column into table?

Thank you in advanced Adam,

Private Sub cmdSubmit_Click()
If IsNull(Me.lstAddedProd) And Not IsNull(Me.lstProduct) Then
  'If IsNull(Forms!frmINVOICE!clientFK) Then MsgBox "You need to ENTER something Dude."


Dim db As DAO.Database
Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone

  With rs
  
    .AddNew
    
    !productName = Me.lstProduct.Column(0)
    '!UnitPrice = Me.lstProduct.Column(1)
    
    .Update
    
  End With

    Me.lstAddedProd.Requery
    Me.lstProduct.Requery
  
    'Me.lstAddedProd = Null
    'Me.lstProduct = Null

Set rs = Nothing

  End If
End Sub

 

[ajetrumpet]

Matt,

What's with the code in red? Why are there quote marks before those lines? That character is meant to indicate message lines rather than code in the modules. The way you have it written now, those two lines will not run. So, from the code below, what you will get in the RecordSetClone table is a new record with only the Product Name added to the new record. Also, I'm not sure why you have to IF's at the front here, but that's up to you I guess. Your process is correct. With a multi-column list box, you add a new record by the columns in the box the same way you have done it. If you're not seeing anything in the table, after running this, I would guess one of the following...

** Your form is not bound, thus providing no basis for a RecordSetClone

** You have referenced the "Me." sections of the form incorrectly, or the "Me." sections actually refer to controls on another form, or subform, which would result in invalid syntax

** You have unintentionally nested IF, THEN statements at the beginning of the code. I underlined the start of the conditionals ("THEN" words).
The front of your code says: "If the conditions of the two product controls are met, then check to see if the clientFK field is NULL. If that is, then give the user a message box, but after that message box is closed, add the new record to the table, as I have indicated with my code". Is that what you mean to say by this?

Private Sub cmdSubmit_Click()
If IsNull(Me.lstAddedProd) And Not IsNull(Me.lstProduct) [B][U]Then[/U][/B]
  [color=red][b]'If IsNull(Forms!frmINVOICE!clientFK)[/color] [U]Then[/U] [color=red]MsgBox "You need to ENTER something Dude."[/b][/color]


Dim db As DAO.Database
Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone

  With rs
  
    .AddNew
    
    !productName = Me.lstProduct.Column(0)
    [color=red][b]'!UnitPrice = Me.lstProduct.Column(1)[/color][/b]
    
    .Update
    
  End With

    Me.lstAddedProd.Requery
    Me.lstProduct.Requery
  
    [color=red][b]'Me.lstAddedProd = Null[/b][/color]
    [color=red][b]'Me.lstProduct = Null[/b][/color]

Set rs = Nothing

  End If
End Sub

USE THIS POST INSTEAD OF THE LAST ONE. SORRY FOR THE DOUBLE!!

 

[gcoaster]

Thank you for your help.

Thank you Adam! it is working now.
the code in the red was just backup code as i was learning. thank you!
you are awesome

 

[ajetrumpet]

Good deal! Good luck with the continued project.