Problem with append query for multiselect Combo Box (has 2 table)

[ahmadrezaahmad]

hello


"I have 1 "main" access file and "Portable".
in the form of main I create buttom to open and apped the table of other access file-portable.accdb- to the main table!"


I had a problem before about attachment field appending and solved that problem in this link.


plz see that problem and download attachment of that topic.
but I want to append a table with multi select combo box.
that combo box field has query from table "list" and i want to append this 2 table (asli & list) to a main database!!!
what can I do?
attachment instruction:
1-solved pervious problem(OK)
2-problem with combobox query(has ERRROR)


tnx!!!

[June7]

Attachment field is a type of multi-value field so the code for multi-value text field is similar, difference is don't have to save file out to folder.

Review https://www.accessforums.net/program...vba-21259.html

[ahmadrezaahmad]

tnx June for your reply...
I saw your link completly but I'm beginner in vba ...
please help me to know how I can append a table with multi_select_combo_box that source of its list from query of another table...
basically we must append 2 table in our access target database!!!
tnx a lot..

[June7]

You got the code for the attachment field to work? The code for multi-value field is basically the same. Just don't need the part about saving file out to folder.

Still have to open the parent and child recordsets of the source data and destination tables. Adapt the example code to your db.

[ahmadrezaahmad]

tnx again june7!
I changed the code completly similar to pervious code but te file not work.
plaes see file main.accdb in folder "2-problem with combobox query(has ERRROR)" in attachment's of 1st post.
i cant seen a code for save file...
please attach worked file.
the cod for append buttom in main.accdb is :
"

Private Sub Command0_Click()
On Error GoTo ErrHandler
Dim strSql As String, fd As FileDialog

Set fd = FileDialog(msoFileDialogFilePicker)
With fd
.AllowMultiSelect = True
.Filters.Add "Access Files", "*.mdb;*.accdb", 1
.InitialFileName = CurrentProject.Path
If .Show Then
strPath = .SelectedItems(1)
Else
Exit Sub
End If
Dim ctr As Container, doc As Document
Dim tdf As TableDef
Dim dbs_connect As dao.Database, dbs As dao.Database

Set dbs = CurrentDb
Set dbs_connect = DBEngine.Workspaces(0).OpenDatabase(fd.SelectedIte ms(1)) ' ahtCommonFileOpenSave)
Set ctr = dbs_connect.Containers!Tables
For Each doc In ctr.Documents
If Not Left(doc.Name, 4) = "MSys" And Not Left(doc.Name, 1) = "~" Then '<>Systemfiler
Set tdf = CurrentDb.CreateTableDef(doc.Name & "1")
tdf.Connect = ";DATABASE=" + fd.SelectedItems(1) 'ahtCommonFileOpenSave
tdf.SourceTableName = doc.Name
CurrentDb.TableDefs.Append tdf
End If
Next doc
End With
If MsgBox("REWRITE?", vbYesNo + vbQuestion) = vbYes Then
strSql = "DELETE * FROM asli " & _
"WHERE [to] IN(SELECT [to] FROM asli1);"
DoCmd.RunSQL strSql
End If
Call InsertData

Exit Sub
ErrHandler:
If Err = 3078 Then
MsgBox "NOT COMPATILBLE TABLE!"
ElseIf Err.Number = 3012 Then
dbs.Execute ("Drop table " & doc.Name & "1")
Resume
Else
MsgBox "Error" & Err & " " & Err.Description
End If
End Sub


Private Sub InsertData()
Dim dbs As dao.Database, rst As Recordset, rstInsert As Recordset


Set dbs = CurrentDb
Set rstInsert = dbs.OpenRecordset("SELECT [from], [to], describtion, [time], [attach].filedata,[attach].filename, [attach].filetype FROM asli ")
Set rst = dbs.OpenRecordset("SELECT [from], [to], describtion, [time], [attach].filedata,[attach].filename, [attach].filetype " _
& "FROM asli1 WHERE to Not IN(SELECT to FROM asli)")
If Not rst.EOF Then
Do
If Not IsNull(rst![attach.Filedata]) Then
rstInsert.AddNew
rstInsert![From] = rst![From]
rstInsert![To] = rst![To]
rstInsert![describtion] = rst![describtion]
rstInsert![Time] = rst![Time]
rstInsert![attach.Filedata] = rst![attach.Filedata]
rstInsert![attach.filename] = rst![attach.filename]
rstInsert.Update
End If
rst.MoveNext
Loop Until rst.EOF
End If
End Sub
"
I should change which part of this code?
again saylease help me with attach worked file
tnx...

[June7]

Why is this code involving attachment field? Why haven't you adapted the code for multi-value field from the link in post 2?

[ahmadrezaahmad]

Im so beginner in vba please insert attachment sample file of your post 2 or adapted my file.
but I tried to remove involving attachment field and adapted the code for multi-value field from the link in post 2:

Option Compare Database


Private Sub Command0_Click()
On Error GoTo ErrHandler
Dim strSql As String, fd As FileDialog

Set fd = FileDialog(msoFileDialogFilePicker)
With fd
.AllowMultiSelect = True
.Filters.Add "Access Files", "*.mdb;*.accdb", 1
.InitialFileName = CurrentProject.Path
If .Show Then
strPath = .SelectedItems(1)
Else
Exit Sub
End If
Dim ctr As Container, doc As Document
Dim tdf As TableDef
Dim dbs_connect As DAO.Database, dbs As DAO.Database

Set dbs = CurrentDb
Set dbs_connect = DBEngine.Workspaces(0).OpenDatabase(fd.SelectedIte ms(1)) ' ahtCommonFileOpenSave)
Set ctr = dbs_connect.Containers!Tables
For Each doc In ctr.Documents
If Not Left(doc.Name, 4) = "MSys" And Not Left(doc.Name, 1) = "~" Then '<>Systemfiler
Set tdf = CurrentDb.CreateTableDef(doc.Name & "1")
tdf.Connect = ";DATABASE=" + fd.SelectedItems(1) 'ahtCommonFileOpenSave
tdf.SourceTableName = doc.Name
CurrentDb.TableDefs.Append tdf
End If
Next doc
End With
' If MsgBox("REWRITE?", vbYesNo + vbQuestion) = vbYes Then
' strSql = "DELETE * FROM asli " & _
' "WHERE [to] IN(SELECT [to] FROM asli1);"
' DoCmd.RunSQL strSql
' End If
Call InsertData

Exit Sub
ErrHandler:
If Err = 3078 Then
MsgBox "NOT COMPATILBLE TABLE!"
ElseIf Err.Number = 3012 Then
dbs.Execute ("Drop table " & doc.Name & "1")
Resume
Else
MsgBox "Error" & Err & " " & Err.Description
End If
End Sub


Private Sub InsertData()
Dim dbs As DAO.Database, rsNew As Recordset, rsOld As Recordset


Set dbs = CurrentDb
Set rsOld = dbs.OpenRecordset("SELECT [from], [to], describtion, [time], [attach].filedata,[attach].filename, [attach].filetype FROM asli ")
Set rsNew = dbs.OpenRecordset("SELECT [from], [to], describtion, [time], [attach].filedata,[attach].filename, [attach].filetype " _
& "FROM asli1 WHERE to Not IN(SELECT to FROM asli)")
Do While Not rsOld.EOF
rsNew.AddNew
rsNew!Id = rsOld!Id
'the value property returns another recordset
Set rsMVold = rsOld.Fields("from").Value
Set rsMVnew = rsNew.Fields("from").Value
Do While Not rsMVold.EOF
rsMVnew.AddNew
rsMVnew.Fields(0) = rsMVold.Fields(0)
rsMVnew.Update
rsMVold.MoveNext
Loop
rsNew.Update
rsOld.MoveNext
Loop
End Sub


----------------------------------------------------------------\
but error ="NOT COMPATILBLE TABLE!"

[June7]

Provide your db with the attempted new code.

[ahmadrezaahmad]

I re attempt my file and it work but it have one big bug!!!
I attach my bug pic!!!
please see to know my file bug!
the bug is:
I want append bottom at first append table list of portable.accdb to table list of main.db then append record of asli table of portable.accdb to asli table of main.accdb so the [from] field of appended record is from the value that user enterd in "list" table of portable.accdb!!!
basically the list table of tow data bas not same!!!!

pleas help june7!!
tnx

[June7]

The list tables have different data. This isn't going to work if every portable db will have its own list table and you want to use autonumber record ID and save it to the multi-value field.

[ahmadrezaahmad]

So what's your solution?
Suppose Table list has: ID , name and family and the other details of our staff!
if your solution is Deleting ID field,I say:By Deleting Field ID , name and family is the source selection list, so if 2 name is same , even if the two name has different family name ,the access show error!!!

[June7]

Options:

1. complex code that during import will assign a new ID value to each of the imported list items and use this new ID in the multi-value field for the imported records

2. pre-establish custom ID that will be unique across all the databases

[ahmadrezaahmad]

tnx
can you learn to me or link me to how I can use per-establish id ?
2.complex code is achievable?u can help me?

[June7]

You decide what scheme you want to use for unique ID. Maybe portable db one uses A1, A2, A3, etc.; portable db two uses B1, B2, B3, etc. Users can manually enter this ID into each list record or can use code to automate generation. Topic of custom unique ID is common in forum.

This would be so much simpler if you did not save an ID and instead just saved the associated descriptive value. What are the descriptors and how long would they be?

[ahmadrezaahmad]

I build one portable.accdb but each user has 1 user name and 1 password.
you guess how I can add field in asli and list table to track the user!
attention:

the query of combo box include:[ID],[staff_name],[staff_family]
and when user select some staff combo box finally show some number(ID of selected staff)

I ask:can I customize ID in vba that automatically add perfix to ID autonumber,which sync with last user username!!!???