Help With Simple Error on Dlookup and Insert To

[asdaf3814]

Hello All,



Long time lurker, first time poster. This is a great site full of information and guides. I use this alot for research on errors and general information on what I am doing wrong. Thank you for all of the contributors that help everyone out!

But I am here because I could not find an answer for my problem, which should be pretty easy but I'm not experienced enough to see it clearly; or I should maybe step away for a little bit.

Overview: I have a main table of customer names,accounts, countries, etc etc. I have a created a form that has a list box and a combo box inside. The combo box stores all of the customer names and allows the user to select one. The list box stores the associated countries with each customer name. The idea is to put insert all of the account numbers into a table that match customer name and their respective country.

I have set this up through a button that runs on the first click. I have two different ways of coding this, both with small errors.

This one returns an error stating it could not could not find the field "|" in the expression:

Private Sub but_add_accounts_Click()
Dim Result As Variant
Dim strSql As String
Dim StrWhere As String
'Clean old accounts first
DoCmd.RunSQL "DELETE * FROM AccountsForForms"
'Matches data selected from Combo/List box in the form to the table with accounts
'ParentNameAndAccounts = Form name. ParentNames = Combobox name inside the Form. AccountList = Listbox name inside the Form
StrWhere = [Parent_Accounts].[P_Name] = Me.ParentNameAndAccounts.[ParentNames] And [Parent_Accounts].[AC_Cntry] = Me.ParentNameAndAccounts.[AccountList]
Result = DLookup("[AC_NR]", "[Parent_Accounts]", " & StrWhere & ")
'Insert the matches account #'s into the new seperate account table named: AccountsForForms.
strSql = "INSERT INTO AccountsForForms ([ACCOUNTS]) " & " VALUES('" & varResult & "')"
Debug.Print strSql
DoCmd.RunSQL strSql, dbFailOnError
Beep
MsgBox "Accounts Added", vbInformation
End Sub

If I use this slightly altered version, I get no errors but it returns nothing into the table. Using the debugger/step-into method I can see it is returning null on the criteria of where it states AC_Cntry = Account list. But I can not figure out why.

Private Sub but_add_accounts_Click()
Dim Result As Variant
Dim strSql As String
'Clean old accounts first
DoCmd.RunSQL "DELETE * FROM AccountsForForms"
'Goal is to Match data selected from Combo/List box in the form to the table with accounts
'ParentNameAndAccounts = Form name. ParentNames = Combobox name inside the Form. AccountList = Listbox name inside the Form
'Insert the matches account #'s into the new seperate account table named: AccountsForForms.
Result = DLookup("[AC_NR]", "Parent_Accounts", P_Name = [Forms]![FrmPOSTCOST]![ParentNameAndAccounts]![ParentNames] And AC_Cntry = [Forms]![FrmPOSTCOST]![ParentNameAndAccounts]![AccountList])
strSql = "INSERT INTO AccountsForForms ([ACCOUNTS]) " & " VALUES('" & varResult & "')"
Debug.Print strSql
DoCmd.RunSQL strSql, dbFailOnError
Beep
MsgBox "Accounts Added", vbInformation
End Sub

Any help or guidance would be greatly appreciated!

[jzwp11]

Is the button on the same form as the combo and list boxes? Is that form called ParentNameAndAccoutns? If so, you would just use the "me" shorthand to refer to the current form. You would not need this:

me.ParentNameAndAccounts
You would just need this: me.

The me is a substitution for the form name

Can only 1 item be selected from the list box or multiple items?

[asdaf3814]

Is the button on the same form as the combo and list boxes? Is that form called ParentNameAndAccoutns? If so, you would just use the "me" shorthand to refer to the current form. You would not need this:

me.ParentNameAndAccounts
You would just need this: me.

The me is a substitution for the form name

Can only 1 item be selected from the list box or multiple items?

Hey Jzwp,

Thanks for your reply and I apologize for not clarifying that bit of information at the start.

The combo and list box are on a seperate form, the form that the combo and list box is on is called ParentNameandAccounts.
The combo box can only have one selection. The list box has the "extended" property set so you can have many selections.

The main form that has the button and a few other things is called: FrmPostCost


You mean in the criteria for the DLookup, as in P_Name = Me.ParentName instead of P_Name = Form!Form!ParentName ? I will give this a try when I get back to work in the morning.

Any other possible solutions, or do you think it is strictly a problem with correct referencing?

Thanks alot for your help, it is greatly appreciated.

[jzwp11]

So the button that executes the code is on frmPostCost but the list and combo box are on a separate form? or a subform on frmPostCost?

The list box has the "extended" property set so you can have many selections.

Do you mean that the multi-select property is set to extended? If so, you have a multiselect list box. If that is indeed the case, you have to loop through the items selected in the list box. So your append query will have to be executed once for every item selected in the list box.

You will need some code like this:

Dim frm As Form, ctl As Control
Dim varItm As Variant
Set frm = Forms!Formname
Set ctl = frm!listboxname
If ctl.ItemsSelected.Count > 0 Then
For Each varItm In ctl.ItemsSelected

execute append query here

Next varItm
Else
MsgBox "No rows have been selected"
End If

Out of curiosity what is the purpose of the Dlookup() in your code since you assign the Dlookup() to a variable called result, but you do not use that anywhere (the append query uses the variable varResult.

[asdaf3814]

So the button that executes the code is on frmPostCost but the list and combo box are on a separate form? or a subform on frmPostCost?



Do you mean that the multi-select property is set to extended? If so, you have a multiselect list box. If that is indeed the case, you have to loop through the items selected in the list box. So your append query will have to be executed once for every item selected in the list box.

You will need some code like this:

Dim frm As Form, ctl As Control
Dim varItm As Variant
Set frm = Forms!Formname
Set ctl = frm!listboxname
If ctl.ItemsSelected.Count > 0 Then
For Each varItm In ctl.ItemsSelected

execute append query here

Next varItm
Else
MsgBox "No rows have been selected"
End If

Out of curiosity what is the purpose of the Dlookup() in your code since you assign the Dlookup() to a variable called result, but you do not use that anywhere (the append query uses the variable varResult.

Jzwp,

Yes, check and list box are a subform on the frmPostCost where the button is located.

Hmm doing the count method makes sense, I didn't think about this approach.

I was doing, or trying rather, to use the Dlookup() because sometimes there can be alot of account numbers that match a country with a parent name. You could have 1 account number or several hundred account numbers attached to Parent Name and Account country. So my thinking was to set a string that pulled these account numbers when you matched the parent name with an account country.

I believe the varResult is a typo, as I don't have that declared anywhere. It should be Result as you mentioned and not varResult.

[jzwp11]

Is the main form bound to a table? If so what type of information is in that table? When you are viewing the main form, does it only show 1 record at a time? If so how does that one record relate to what you are trying to do with the subform? Incidently, why create the records in AccountsForForms & then delete them before you recreate them?

Could you explain the business process that is prompting you to do this append? Something does not sound right to me.

[asdaf3814]

Is the main form bound to a table? If so what type of information is in that table? When you are viewing the main form, does it only show 1 record at a time? If so how does that one record relate to what you are trying to do with the subform? Incidently, why create the records in AccountsForForms & then delete them before you recreate them?

Could you explain the business process that is prompting you to do this append? Something does not sound right to me.

This form frmPostCost is not bound to a table. I have another subform in this form that is bound to a table however. It is the table that I was trying to insert the results of the DLookup() into.

This form is the first form. Aka Step 1. This allows the selection of accounts to be used. In this form, there are a few buttons, one to toggle between subforms (One being the problem, and the other another where it shows the accounts or you can copy/paste/delete them there.) Two other buttons. The main button is where I'm having the problem and brought me to posting this question. The other button, basically closes this form and continues to the next step.

So you have the main Form: frmPostCost
subform: ParentNameAndAccounts
subform: Accounts. (Bound to a table; specifically this table: strSql = "INSERT INTO AccountsForForms ([ACCOUNTS]) " & " VALUES('" & varResult & "')"
Two buttons
One Stop/Exit
A "toggle" button for switching between the subforms.


The reason for the delete, is because I have this process pretty much localized, and it's to continue from keeping the account numbers being stored into that table. It was set up to be stored on the SQL server, but this is temporarily not feasible unfortunately. So I thought this was the easiest and best solution to be used multiple times by numerous people.

The next form is where I'm pulling data using these accounts. There are alot of queries and options for pulling data, and selecting certain things, as well as random functions, etc.

The final step is an output, where it displays the data pulled into a certain format and gives the option for outputting into a text file or an excel dbf file.

[jzwp11]

Here is what I would guess the code would look like (not tested of course). I'm still not sure if the dLookup() will do what you want. If you want the records to go into another table then I then you will need another append query.

Private Sub but_add_accounts_Click() Dim Result As Variant Dim strSql As String 'Clean old accounts first DoCmd.RunSQL "DELETE * FROM AccountsForForms" 'Goal is to Match data selected from Combo/List box in the form to the table with accounts 'ParentNameAndAccounts = Form name. ParentNames = Combobox name inside the Form. AccountList = Listbox name inside the Form 'Insert the matches account #'s into the new seperate account table named: AccountsForForms. Result = DLookup("[AC_NR]", "Parent_Accounts", P_Name = [Forms]![FrmPOSTCOST]![ParentNameAndAccounts]![ParentNames] And AC_Cntry = [Forms]![FrmPOSTCOST]![ParentNameAndAccounts]![AccountList]) Dim frm As Form, ctl As Control
Dim varItm As Variant
Set frm = Forms!ParentNameAndAccounts Set ctl = frm!AccountList If ctl.ItemsSelected.Count > 0 Then
For Each varItm In ctl.ItemsSelected


strSql = "INSERT INTO AccountsForForms ([ACCOUNTS]) " & " VALUES('" & varItm & "')" Debug.Print strSql DoCmd.RunSQL strSql, dbFailOnError Beep Next varItm
Else
MsgBox "No rows have been selected"
End If


MsgBox "Accounts Added", vbInformation End Sub

[asdaf3814]

Hey Jzwp11,

Thanks alot for your help and replies. It got me thinking, and I re-did the code and got it to work. I went with your approach for the count method and dumped the DLookup. I decided to do an insert/count based on each selection from the list box.

Here is what finally worked in the end:

Private Sub but_add_accounts_Click()
'Goal is to Match data selected from Combo/List box in the form to the table with account
Dim strSQL As String
Dim strName As String
Dim varItem As Variant
Dim ParentalName As String
Dim strTEST As String
 
DoCmd.RunSQL "DELETE * FROM AccountsForForms"

With Me.ParentNameAndAccounts!AccountList
    If .MultiSelect = 0 Then
        ParentalName = Me.ParentNameAndAccounts!ParentNames
        strName = .Value
         strSQL = "INSERT INTO AccountsForForms (ACCOUNTS)SELECT [Parent_Accounts2]![AC_NR] FROM [Parent_Accounts2] WHERE ([Parent_Accounts2]![P_Name]= " & "'" & ParentalName & "'" & " AND [Parent_Accounts2]![AC_Cntry]= " & "'" & strName & "'" & " )"
        CurrentProject.Connection.Execute strSQL
        
     Else
        For Each varItem In .ItemsSelected
            ParentalName = Me.ParentNameAndAccounts!ParentNames
            strName = .Column(0, varItem)
            strSQL = "INSERT INTO AccountsForForms (ACCOUNTS)SELECT [Parent_Accounts2]![AC_NR] FROM [Parent_Accounts2] WHERE ([Parent_Accounts2]![P_Name]= " & "'" & ParentalName & "'" & " AND [Parent_Accounts2]![AC_Cntry]= " & "'" & strName & "'" & " )"
            CurrentProject.Connection.Execute strSQL
        Next varItem
    End If
End With
Beep
MsgBox "Process Finished ", vbInformation
End Sub

[jzwp11]

You're welcome. I'm glad we were able to help you reach a solution.