H
hughess7
Hi all
I am trying to select a table to filter records via code but it is not
working, I get error 3078 - can not find input table or query... but the
syntax returned looks ok to me and I'm sure it worked last week? Can anyone
help shed any light on this please? Or tell me a better way of doing it if
this isn't the best way...
I have two unbound fields - Country and Dealer, both text fields. The user
enters this information and then I want to create a subset of data from the
correct table (each country has a different table). I want to append the data
from the correct countries table to a table called Claim Data (which has same
field names).
The code I have is (I will add insert into etc if i can 1st get this bit
working):
Dim NextAudit, MyTable As String
Dim txtCountry As String, txtDealer As String, MyDate As Date
Dim strSql As String
NextAudit = DLookup("MaxofAuditNo", "[qry AuditNos EGP]", "[DealerCode]=" &
"""" & Me![Dealer] & """")
If IsNull(NextAudit) Then
Me![AuditNo] = 1
Else
Me![AuditNo] = NextAudit + 1
End If
txtCountry = Me![CountryCode]
txtDealer = Me![Dealer]
MyDate = Date - 1100
MyTable = "[Warranty Data " & txtCountry & "]"
strSql = "SELECT * FROM " & MyTable & " WHERE Dealer_Code=" & """" &
txtDealer & """" & " AND SBI_Date>" & Format(MyDate, "\#mm/dd/yyyy\#")
Me.RecordSource = strSql
Me![ClaimCount] = DCount("*", strSql)
If Me![ClaimCount] > 0 Then
Me![ClaimData].Enabled = True
Else
Me![ClaimData].Enabled = False
End If
I am trying to select a table to filter records via code but it is not
working, I get error 3078 - can not find input table or query... but the
syntax returned looks ok to me and I'm sure it worked last week? Can anyone
help shed any light on this please? Or tell me a better way of doing it if
this isn't the best way...
I have two unbound fields - Country and Dealer, both text fields. The user
enters this information and then I want to create a subset of data from the
correct table (each country has a different table). I want to append the data
from the correct countries table to a table called Claim Data (which has same
field names).
The code I have is (I will add insert into etc if i can 1st get this bit
working):
Dim NextAudit, MyTable As String
Dim txtCountry As String, txtDealer As String, MyDate As Date
Dim strSql As String
NextAudit = DLookup("MaxofAuditNo", "[qry AuditNos EGP]", "[DealerCode]=" &
"""" & Me![Dealer] & """")
If IsNull(NextAudit) Then
Me![AuditNo] = 1
Else
Me![AuditNo] = NextAudit + 1
End If
txtCountry = Me![CountryCode]
txtDealer = Me![Dealer]
MyDate = Date - 1100
MyTable = "[Warranty Data " & txtCountry & "]"
strSql = "SELECT * FROM " & MyTable & " WHERE Dealer_Code=" & """" &
txtDealer & """" & " AND SBI_Date>" & Format(MyDate, "\#mm/dd/yyyy\#")
Me.RecordSource = strSql
Me![ClaimCount] = DCount("*", strSql)
If Me![ClaimCount] > 0 Then
Me![ClaimData].Enabled = True
Else
Me![ClaimData].Enabled = False
End If