list box not populating table when set to simple

[cedtech23]

created a main form called "frmPatientDemographics" that contains

txtFirstName (Text Box)
txtLastName (Text Box)
txtDOB (Text Box)
txtCountry (Text Box)
cboGender (Combo Box)

it's record source is a table called "tblPatientDemo" that are populated based
on the information place in the above text boxes

Here is my problem

I have a subform called "frmPatientLanguageSub"

Source Object......... frmPatientLanguge
link Child Fields..... PatientID
link Child Fields..... PatientID

When list box Multi Select is set to "Simple" and I add a new patient and click on
multiple selections in the list box it does not populate the "tblPatientLanguge"

But if I change the Multi Select is set to "None" it populates the "tblPatientLanguge"
for that patient

Any Ideas why access populates the "tblPatientLanguge" if I set the Multi Select property to "None" but not for "Simple"?
If so How do I make access poplulate when the table when muliple selections are selected in the list box?

 

[pbaldy]

Sure; a multiselect listbox always has a value of Null. How to do what you want depends on your table structure, but searching here on "multiselect" should turn up numerous threads with the relevant code.

 

[cedtech23]

OK I did a search but was more confused

How do I get the values that are selected in the list box
using a for statement?

Please can you include comments so I know what's going on in the code?

 

[cedtech23]

I am getting the following error

"Run-Time Error '3201':
You cannot add or change a record because a related record is required in
table 'tblPatientDemo' "

I placed the following code in a command button

Private Sub cmdOK_Click()



Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Dim varSelected As Variant

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

With cn
  .Provider = "Microsoft.Jet.OLEDB.4.0"
  .Properties("Data Source") = "c:\Documents and Settings\cspence\Desktop\CHCB_Projects\NewAmericans.mdb"
'  .Properties("Jet OLEDB:System database") = "\\serv1\db\NCAA.mdw"
'  .Properties("User ID") = TheUserID
'  .Properties("Password") = ThePassword
'  .Properties("Persist Security Info") = False
  .Properties("Mode") = adModeShareDenyNone
  .Open
End With


With rs
   .ActiveConnection = cn
   .CursorLocation = adUseServer
   .CursorType = adOpenStatic
   .LockType = adLockOptimistic
   .Source = "SELECT * FROM tblPatientLanguage"
   .Open
End With


For Each varSelected In Me!lstLanguage.ItemsSelected
            rs.AddNew
            rs("Language") = Me!lstLanguage.ItemData(varSelected)
            rs.Update
    Next varSelected

Set db = Nothing   ' Clear db...
MsgBox ("This data is now in your table..")
 
    
End Sub

The above code will cycle through the list box and enter the value in a table
called "tblPatientLanguage" in column "Language"

Table tblPatientDemo has a one to many relationship with tblPatientLanguage
the PatientID column being the key


The list box is contained in a subform so it should be link with the PatientID from
the parent form..

So why am I getting this error? I attached the file if someone can help me out

thanks

 

[pbaldy]

Haven't looked at your sample, but while the listbox may be on a subform bound to the table, the code is working directly with the table. Therefore, you'll need to add the PatientID field yourself, something like:

rs.AddNew
rs("PatientID") = Me.PatientID
rs("Language") = Me!lstLanguage.ItemData(varSelected)
rs.Update

 

[cedtech23]

I am getting a new error know

Private Sub cmdOK_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Dim varSelected As Variant


Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

With cn
  .Provider = "Microsoft.Jet.OLEDB.4.0"
  .Properties("Data Source") = "C:\Documents and Settings\Cedric\Desktop\1176443-PTDBVer1"
'  .Properties("User ID") = TheUserID
'  .Properties("Password") = ThePassword
'  .Properties("Persist Security Info") = False
  .Properties("Mode") = adModeReadWrite
  .Open
End With


With rs
   .ActiveConnection = cn
   .CursorLocation = adUseServer
   .CursorType = adOpenStatic
   .LockType = adLockOptimistic
   .Source = "SELECT * FROM tblPatientLanguage"
   .Open
End With


For Each varSelected In Me!lstLanguage.ItemsSelected
            rs.AddNew
            rs("PatientID") = [Forms]![frmPatientDemographics]![PatientID]
            rs("Language") = Me!lstLanguage.ItemData(varSelected)
            rs.Update
    Next varSelected

Set db = Nothing   ' Clear db...
MsgBox ("This data is now in your table..")
 
End Sub

but know I get this error


run-time error '-2147467259 (80004005)':

The microsoft jet database engin cannot open the file
it's already opened exclusively by another user, or you need
permission to view its data


which does not make sense because the file has no permissions set. Can you take a look at it and see where I messed up.
again thanks I have a better understanding of the structure that I need in regards to subforms

 

[cedtech23]

Man is this thing driving me batty I figured that the path to the file was the issue
but still I can into another road block. I am know getting the following error

"run-time error '3265':

item cannot be found in the collection to the requested name or ordinal"

debug brings me to

Code:


For Each varSelected In Me!lstLanguage.ItemsSelected
rs.AddNew
rs("PatientID") = [Forms]![frmPatientDemographics]![PatientID]
rs("Language") = Me!lstLanguage.ItemData(varSelected)
rs.Update
Next varSelected



I can't deciper that error??

 

[cedtech23]

rs("LanguageID") = Me!lstLanguage.ItemData(varSelected)

I was refering to the wrong column name. I guess a night sleep helps a lot