Append Query From Form Syntax Error

[burrina]

If DCount("*", "[TReg]", "[Bank] = " & RegID) <= 0 Then 'Purpose: Check is Bank Account Exists in TReg Table.

Table being used on form is TBanks and table to apend to is TReg
I wanted to check and see if the Bank was in the table and if not append it.


Append query is appending form TBanks table to TReg table.

Above code gives me an error saying; Syntax error (missing operator) in query expression {bank}
There is ONLY one field in the append query and it is Bank and it is found in both tables.
Bank field in TReg table uses limit to list, is this a problem? Bank field looks up Bank form table TBanks as a combo?
Table TBanks has its Primary Key as bankid

[June7]

You say the DCount line is triggering error? I don't see anything wrong with DCount.

The DCount refers to table and has nothing to do with APPEND query. Nor should the combobox LimitToList property bear on the DCount.

[burrina]

It says syntax error in RegID so I have not gotten the code correct. RegID is the primary key in the check register and I am using this code on a form to try and open the check register and MAKE it keep the same bank account. Check Register form is a subform, a continuous subform at that and it uses a Union Query as its record source. On the main form it uses the same table as the subform just not a query. Bank is the field I want to keep as the same for all records on the subform. Maybe it can't be done? Anyway the code as it is will not work.

[June7]

This code is behind a form? Is RegID in the RecordSource of the form? RegID is the parameter to restrict the DLookup search in TReg table. If RegID is not coming from the current record on the form, it is meaningless.

[burrina]

Yes, it is coming from a form that in theory is supposed to open the check register with that selection form the combo on the form that lists all of the banks. Choose a bank and then the check registry opens with that bank ONLY. Here is the code that is run. YES RegID is in the record source.
On Error GoTo Err_ChkRegstr_Click

If DCount("*", "[TReg]", "[RegID] = " & Bank) <= 0 Then 'Purpose: Check is Bank Account Exists is TReg Table.
DoCmd.RunCommand acCmdSelectRecord
Me.bankid.DefaultValue = """" & Me.bankid.Value & """"

Dim stDocName As String 'Purpose: Open Check Register W/Selected Bank Account.
Dim stLinkCriteria As String

stDocName = "Copy Of FReg"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, Me.Name

Exit_ChkRegstr_Click:

Exit Sub

Err_ChkRegstr_Click:
MsgBox Err.Description
Resume Exit_ChkRegstr_Click
'End If
End Sub

[June7]

You show the RegID and Bank reversed in this code. Code still fails on this line?

RegID is a number field. Is the combobox value also a number?

[burrina]

Combo looks up both so that the ID is shown on the form but ONLY Bank is shown in the list. YES, RegID is a Primary Key "Number"

[June7]

Sounds like the DLookup is set up properly. I can't fathom why it isn't working.

[burrina]

Me either. Thanks though. I will mark this as solved and move on. Bigger fish to fry! Thanks again.