Selecting data

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
 
M

Michael J. Strickland

hughess7 said:
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] & """")

Is this code in a form or report?

I think "Me" here refers to your form/report name which is probably not
what you want.

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



--
 
H

hughess7

Yes it refers to my form - why is that not what I want?

For reference, the NextAudit bit of the code works. It is counting if any
records are returned which is the problem. Even if I take out the
Recordsource bit and just try to write to strSql I still get the same error -
it doesn't go any further.



Michael J. Strickland said:
hughess7 said:
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] & """")

Is this code in a form or report?

I think "Me" here refers to your form/report name which is probably not
what you want.

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



--
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top