Creating data via code

[hughess7]

Hi all

I am trying to create a set of data on a form by way of a recordset. The
user enters a Country and a dealer (in unbound combos) and I want the data
displayed on the form if any exists, with a count of records, OR a message
stating no data to claim if not.

I have the following code but it produces an error '2001: You cancelled the
previous operation' on the DCount line I think.

This is Part 1 of my task.

Part 2 is then to write the data found (if any) to an existing table called
[Claim Data]. The difficult part being that the fields don't match so at the
moment we use an append query to populate the table - is this possible still
somehow?

For reference, the database I am using is an inherited one and it has a lot
of pre-existing objects (tables, forms, queries, reports etc) so changing any
field names etc is not a possibility. We work in lots of different countries
throughout Europe. Each country has its own database and they are a large
file, hence combining them would not really work for Access. So each country
has its own linked Table called [Warranty Data x] x being the designated
letter(s) of that country.

I did think about using 'insert into' to write the data to a generic table
called Warranty Data and then still using the existing append queries we
have. Just not sure if this is the best / most efficient way of doing it?

--------------------------
Code:

Private Sub Dealer_AfterUpdate()
Dim NextAudit, MyTable As String
Dim txtCountry As String, txtDealer As String, MyDate As Date
Dim db As DAO.Database
Dim strSQL As String
Dim strWhere 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 MyTable.* FROM MyTable "
strWhere = "MyTable.Dealer_Code=" & """" & txtDealer & """"
strWhere = strWhere & " AND " & "MyTable.SBI_Date>#" & MyDate & "#"

If DCount("*", MyTable, strWhere) = 0 Then
MsgBox "No records that match the chosen criteria.", vbExclamation,
"Note"
Me![ClaimData].Enabled = False
Else
Me.RecordSource = strSQL & strWhere
Me![ClaimData].Enabled = True
End If

End Sub


Thanks in advance for any help.
Sue

 

[Arvin Meyer MVP]

Hi all

I am trying to create a set of data on a form by way of a recordset. The
user enters a Country and a dealer (in unbound combos) and I want the data
displayed on the form if any exists, with a count of records, OR a message
stating no data to claim if not.

I have the following code but it produces an error '2001: You cancelled
the
previous operation' on the DCount line I think.

I don't know why you're using DCount. Here's an example of what you want
using list boxes:

http://www.accessmvp.com/Arvin/Combo.zip

This is Part 1 of my task.

Part 2 is then to write the data found (if any) to an existing table
called
[Claim Data]. The difficult part being that the fields don't match so at
the
moment we use an append query to populate the table - is this possible
still
somehow?

Yes, use form variables as the data to write, or it's just as easy for 1
record, to use a recordset like:

Dim db As DAO.Database
Dim rst As Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("YourTableName")
With rst
.AddNew
!ID = Me.txtID
!SomeField = Me.txtSomeTextbox
' Values for other fields here
.Update
End With

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

For reference, the database I am using is an inherited one and it has a
lot
of pre-existing objects (tables, forms, queries, reports etc) so changing
any
field names etc is not a possibility. We work in lots of different
countries
throughout Europe. Each country has its own database and they are a large
file, hence combining them would not really work for Access. So each
country
has its own linked Table called [Warranty Data x] x being the designated
letter(s) of that country.

I did think about using 'insert into' to write the data to a generic table
called Warranty Data and then still using the existing append queries we
have. Just not sure if this is the best / most efficient way of doing it?

--------------------------
Code:

Private Sub Dealer_AfterUpdate()
Dim NextAudit, MyTable As String
Dim txtCountry As String, txtDealer As String, MyDate As Date
Dim db As DAO.Database
Dim strSQL As String
Dim strWhere 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 MyTable.* FROM MyTable "
strWhere = "MyTable.Dealer_Code=" & """" & txtDealer & """"
strWhere = strWhere & " AND " & "MyTable.SBI_Date>#" & MyDate & "#"

If DCount("*", MyTable, strWhere) = 0 Then
MsgBox "No records that match the chosen criteria.", vbExclamation,
"Note"
Me![ClaimData].Enabled = False
Else
Me.RecordSource = strSQL & strWhere
Me![ClaimData].Enabled = True
End If

End Sub


Thanks in advance for any help.
Sue

 

[hughess7]

Thanks Arvin. I will have a look at the example you sent. I was using DCount
from another posting suggestion someone else had, who was trying to achieve
the same thing - just to check if any data had been returned.

Regarding part 2 of my problem, the reason I was trying to avoid either of
those suggestions was because there are at least 20 fields to match so they
are not all displayed on the form, and I was hoping I could use the existing
append query somehow.

Thanks
Sue

Hi all

I am trying to create a set of data on a form by way of a recordset. The
user enters a Country and a dealer (in unbound combos) and I want the data
displayed on the form if any exists, with a count of records, OR a message
stating no data to claim if not.

I have the following code but it produces an error '2001: You cancelled
the
previous operation' on the DCount line I think.

I don't know why you're using DCount. Here's an example of what you want
using list boxes:

http://www.accessmvp.com/Arvin/Combo.zip

This is Part 1 of my task.

Part 2 is then to write the data found (if any) to an existing table
called
[Claim Data]. The difficult part being that the fields don't match so at
the
moment we use an append query to populate the table - is this possible
still
somehow?

Yes, use form variables as the data to write, or it's just as easy for 1
record, to use a recordset like:

Dim db As DAO.Database
Dim rst As Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("YourTableName")
With rst
.AddNew
!ID = Me.txtID
!SomeField = Me.txtSomeTextbox
' Values for other fields here
.Update
End With

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

For reference, the database I am using is an inherited one and it has a
lot
of pre-existing objects (tables, forms, queries, reports etc) so changing
any
field names etc is not a possibility. We work in lots of different
countries
throughout Europe. Each country has its own database and they are a large
file, hence combining them would not really work for Access. So each
country
has its own linked Table called [Warranty Data x] x being the designated
letter(s) of that country.

I did think about using 'insert into' to write the data to a generic table
called Warranty Data and then still using the existing append queries we
have. Just not sure if this is the best / most efficient way of doing it?

--------------------------
Code:

Private Sub Dealer_AfterUpdate()
Dim NextAudit, MyTable As String
Dim txtCountry As String, txtDealer As String, MyDate As Date
Dim db As DAO.Database
Dim strSQL As String
Dim strWhere 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 MyTable.* FROM MyTable "
strWhere = "MyTable.Dealer_Code=" & """" & txtDealer & """"
strWhere = strWhere & " AND " & "MyTable.SBI_Date>#" & MyDate & "#"

If DCount("*", MyTable, strWhere) = 0 Then
MsgBox "No records that match the chosen criteria.", vbExclamation,
"Note"
Me![ClaimData].Enabled = False
Else
Me.RecordSource = strSQL & strWhere
Me![ClaimData].Enabled = True
End If

End Sub


Thanks in advance for any help.
Sue

 

[Arvin Meyer MVP]

Thanks Arvin. I will have a look at the example you sent. I was using
DCount
from another posting suggestion someone else had, who was trying to
achieve
the same thing - just to check if any data had been returned.

Regarding part 2 of my problem, the reason I was trying to avoid either of
those suggestions was because there are at least 20 fields to match so
they
are not all displayed on the form, and I was hoping I could use the
existing
append query somehow.

Using the append query is an option, but since you are appending from a
form, you would probably need to rewrite it anyway. For a single record, it
has to be faster to write recordset code because for the append query you'd
still need to write the code to use the append query, including passing all
the parameters.

 

[hughess7]

Thanks, it is not a single record though? It could be (although extremely
unlikely), it can be anything from 0 to several thousand records to write to
the table [Claim Data]....

For reference I was wrong before about the field names being different, they
are the same in both tables.

Sue

 

[hughess7]

Hi again Arvin

Your example uses a query to filter the data based on a value in a listbox
on a form and as you state, it only returns one record.

Mine can't use a fixed query, unless I have one per country, as the record
source is in a different table, depending on the country the user enters eg
Warranty Data A for example is the UK, Warranty Data B is Germany etc.

The form is continuous to display all records found (if any). Which is why I
was trying to do it in code (see below). I now get a runtime error 2580,
telling me that the record source was not found. It looks ok apart from it
states the variable MyTable, rather than [Warranty Data A] - so I am not sure
you can reference tables in this way?

MyTable = "Warranty Data " & txtCountry

strSql = "SELECT MyTable.* FROM MyTable "
strWhere = "MyTable.Dealer_Code=" & """" & txtDealer & """"
strWhere = strWhere & " AND " & "MyTable.SBI_Date>#" & MyDate & "#"

Me.RecordSource = strSql & "WHERE " & strWhere

Thanks in advance for any help.
Sue

Hi all

I am trying to create a set of data on a form by way of a recordset. The
user enters a Country and a dealer (in unbound combos) and I want the data
displayed on the form if any exists, with a count of records, OR a message
stating no data to claim if not.

I have the following code but it produces an error '2001: You cancelled
the
previous operation' on the DCount line I think.

I don't know why you're using DCount. Here's an example of what you want
using list boxes:

http://www.accessmvp.com/Arvin/Combo.zip

This is Part 1 of my task.

Part 2 is then to write the data found (if any) to an existing table
called
[Claim Data]. The difficult part being that the fields don't match so at
the
moment we use an append query to populate the table - is this possible
still
somehow?

Yes, use form variables as the data to write, or it's just as easy for 1
record, to use a recordset like:

Dim db As DAO.Database
Dim rst As Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("YourTableName")
With rst
.AddNew
!ID = Me.txtID
!SomeField = Me.txtSomeTextbox
' Values for other fields here
.Update
End With

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

For reference, the database I am using is an inherited one and it has a
lot
of pre-existing objects (tables, forms, queries, reports etc) so changing
any
field names etc is not a possibility. We work in lots of different
countries
throughout Europe. Each country has its own database and they are a large
file, hence combining them would not really work for Access. So each
country
has its own linked Table called [Warranty Data x] x being the designated
letter(s) of that country.

I did think about using 'insert into' to write the data to a generic table
called Warranty Data and then still using the existing append queries we
have. Just not sure if this is the best / most efficient way of doing it?

--------------------------
Code:

Private Sub Dealer_AfterUpdate()
Dim NextAudit, MyTable As String
Dim txtCountry As String, txtDealer As String, MyDate As Date
Dim db As DAO.Database
Dim strSQL As String
Dim strWhere 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 MyTable.* FROM MyTable "
strWhere = "MyTable.Dealer_Code=" & """" & txtDealer & """"
strWhere = strWhere & " AND " & "MyTable.SBI_Date>#" & MyDate & "#"

If DCount("*", MyTable, strWhere) = 0 Then
MsgBox "No records that match the chosen criteria.", vbExclamation,
"Note"
Me![ClaimData].Enabled = False
Else
Me.RecordSource = strSQL & strWhere
Me![ClaimData].Enabled = True
End If

End Sub


Thanks in advance for any help.
Sue

 

[John W. Vinson]

Mine can't use a fixed query, unless I have one per country, as the record
source is in a different table, depending on the country the user enters eg
Warranty Data A for example is the UK, Warranty Data B is Germany etc.

In that case the best choice would be an Append Query from one table into the
other; the Form would not be involved at all, other than perhaps as a source
of criteria to determine which records to append. You would be appending
records from the source table, NOT from the form. After all, the form does not
contain any data; the data is in the Table, and the form is just a window
displaying the data.

 

[hughess7]

Yes getting there i think thanks . I've changed it slightly so my code
returns a record count to check there is data to claim and only enables the
Claim data button if the Claim count returned is greater than 0 records. May
not be the best way to do this but the code below works...

txtCountry = Me![CountryCode]
txtDealer = Me![Dealer]

MyDate = Date - 1100

MyTable = "Warranty Data " & txtCountry

strSql = "SELECT * FROM [" & MyTable & "]"
strWhere = "Dealer_Code=" & """" & txtDealer & """"
strWhere = strWhere & " AND " & "SBI_Date>#" & MyDate & "#"

Me.RecordSource = strSql & " WHERE " & strWhere

Me![ClaimCount] = DCount("*", MyTable, strWhere)

If Me![ClaimCount] > 0 Then
Me![ClaimData].Enabled = True
Else
Me![ClaimData].Enabled = False
End If

My next question is how to use this recordset to create a table of data? The
table [Claim Data] exists as a temp storage table for this purpose - as I
believe creating and deleting tables causes bloating. So is it possible to
use this recordset of data to append to [Claim Data] without using a fixed
query?

Thanks and have a good Easter all...

 

[John W. Vinson]

My next question is how to use this recordset to create a table of data? The
table [Claim Data] exists as a temp storage table for this purpose - as I
believe creating and deleting tables causes bloating. So is it possible to
use this recordset of data to append to [Claim Data] without using a fixed
query?

Sure. Just construct an Append query instead of a simple select query.
Something like this (with some corrections to errors in your query):

strSql = "INSERT INTO [Claim Data] SELECT * FROM [" & MyTable & "]" _
& " WHERE Dealer_Code=" & """" & txtDealer & """" _
& " AND SBI_Date>#" & MyDate & "#"

You'ld then execute strSQL:

CurrentDb.Execute strSQL, dbFailOnError

 

[hughess7]

Hi thanks for this. Strange thing... before Easter I'm sure my code was
working and now I get a runtime error 3078 - The microsoft jet database
engine cannot find the input table or query 'SELECT * FROM [Warranty Data A]
WHERE Dealer_Code="1737" AND SBI_Date>#10/04/06#'.

I've checked it against your version and it looks the same to me, any ideas?

Thanks
Sue

My next question is how to use this recordset to create a table of data? The
table [Claim Data] exists as a temp storage table for this purpose - as I
believe creating and deleting tables causes bloating. So is it possible to
use this recordset of data to append to [Claim Data] without using a fixed
query?

Sure. Just construct an Append query instead of a simple select query.
Something like this (with some corrections to errors in your query):

strSql = "INSERT INTO [Claim Data] SELECT * FROM [" & MyTable & "]" _
& " WHERE Dealer_Code=" & """" & txtDealer & """" _
& " AND SBI_Date>#" & MyDate & "#"

You'ld then execute strSQL:

CurrentDb.Execute strSQL, dbFailOnError

 

[John W. Vinson]

Hi thanks for this. Strange thing... before Easter I'm sure my code was
working and now I get a runtime error 3078 - The microsoft jet database
engine cannot find the input table or query 'SELECT * FROM [Warranty Data A]
WHERE Dealer_Code="1737" AND SBI_Date>#10/04/06#'.

I've checked it against your version and it looks the same to me, any ideas?

Please post your actual code. Is Dealer_Code a Text field?

 

[hughess7]

Hi, thanks John. I will post it when I am back at work tomorrow. Yes Dealer
Code is a text field as some dealers start with a 0 (and some customers have
other text in the code too).

For reference, I tried copying and pasting your code (removing the insert so
it was just a select statement) and I get the same error. Weird thing is my
code I posted earlier did work last week...

Hi thanks for this. Strange thing... before Easter I'm sure my code was
working and now I get a runtime error 3078 - The microsoft jet database
engine cannot find the input table or query 'SELECT * FROM [Warranty Data A]
WHERE Dealer_Code="1737" AND SBI_Date>#10/04/06#'.

I've checked it against your version and it looks the same to me, any ideas?

Please post your actual code. Is Dealer_Code a Text field?

 

[hughess7]

Hi John

Code as promised thanks...

-----------------

Private Sub Dealer_AfterUpdate()
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

End Sub

Hi thanks for this. Strange thing... before Easter I'm sure my code was
working and now I get a runtime error 3078 - The microsoft jet database
engine cannot find the input table or query 'SELECT * FROM [Warranty Data A]
WHERE Dealer_Code="1737" AND SBI_Date>#10/04/06#'.

I've checked it against your version and it looks the same to me, any ideas?

Please post your actual code. Is Dealer_Code a Text field?