|
 |
|
 |
|
Creating data via code
|
|
On Wed, 8 Apr 2009 09:21:02 -0700, hughess7 <...@discussions.microsoft.com
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
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
|
| |
 |
|
 |
|
|
|
|
|
On Wed, 8 Apr 2009 12:57:47 -0400, "Arvin Meyer MVP" <...@mvps.invalid
"hughess7" <...@microsoft.com...
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
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
|
|
|
|
|
|
|
|
|
On Thu, 9 Apr 2009 02:37:01 -0700, hughess7 <...@discussions.microsoft.com
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
>
|
|
|
|
|
|
|
|
|
On Thu, 9 Apr 2009 09:50:34 -0400, "Arvin Meyer MVP" <...@mvps.invalid
"hughess7" <...@microsoft.com...
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.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
|
|
|
|
|
|
|
|
|
On Thu, 9 Apr 2009 07:25:03 -0700, hughess7 <...@discussions.microsoft.com
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
>
|
|
|
|
|
|
|
|
|
On Thu, 9 Apr 2009 08:47:02 -0700, hughess7 <...@discussions.microsoft.com
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
Me.RecordSource = strSql & "WHERE " & strWhere
Thanks in advance for any help.
Sue
>
|
|
|
|
|
|
|
|
|
On Thu, 09 Apr 2009 10:03:26 -0600, John W. Vinson <...@STOP_SPAM.WysardOfInfo.com
On Thu, 9 Apr 2009 08:47:02 -0700, hughess7
<...@discussions.microsoft.com
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.
--
John W. Vinson [MVP]
|
|
|
|
|
|
|
|
|
On Thu, 9 Apr 2009 09:28:01 -0700, hughess7 <...@discussions.microsoft.com
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
Me.RecordSource = strSql & " WHERE " & strWhere
Me![ClaimCount] = DCount("*", MyTable, strWhere)
If Me![ClaimCount] 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...
>
|
|
|
|
|
|
|
|
|
On Thu, 09 Apr 2009 13:06:35 -0600, John W. Vinson <...@STOP_SPAM.WysardOfInfo.com
On Thu, 9 Apr 2009 09:28:01 -0700, hughess7
<...@discussions.microsoft.com
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
You'ld then execute strSQL:
CurrentDb.Execute strSQL, dbFailOnError
--
John W. Vinson [MVP]
|
|
|
|
|
|
|
|
|
On Tue, 14 Apr 2009 02:06:01 -0700, hughess7 <...@discussions.microsoft.com
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
I've checked it against your version and it looks the same to me, any ideas?
Thanks
Sue
>
|
|
|
|
|
|
|
|
|
On Tue, 14 Apr 2009 12:21:16 -0600, John W. Vinson <...@STOP_SPAM.WysardOfInfo.com
On Tue, 14 Apr 2009 02:06:01 -0700, hughess7
<...@discussions.microsoft.com
Please post your actual code. Is Dealer_Code a Text field?
--
John W. Vinson [MVP]
|
|
|
|
|
|
|
|
|
On Tue, 14 Apr 2009 12:08:02 -0700, hughess7 <...@discussions.microsoft.com
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...
>
|
|
|
|
|
|
|
|
|
On Wed, 15 Apr 2009 01:20:02 -0700, hughess7 <...@discussions.microsoft.com
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_DateFormat(MyDate, "\#mm/dd/yyyy\#")
Me.RecordSource = strSql
Me![ClaimCount] = DCount("*", strSql)
If Me![ClaimCount] Me![ClaimData].Enabled = True
Else
Me![ClaimData].Enabled = False
End If
End Sub
>
|
|
|
|
|
|
|