Query and AddNew on DataEnvironment

[Guest_imported]

I have a month view conrtol on a form and a data enviroment connection to database where the user can save a message to a certain date (the date that user clicks)

What is code for Addnew? I have already:

DataEnvironment1.rsTable1.AddNew

but how do i write code to tell which textbox the message is coming from, ie

Someone tell me it is something like:

DataEnvironment1.rsTable1 = Text1.Text

but this not work.

Aslo if the user cliks any date on monthview, what is code for query on the database to call the saved message back into the textbox if one is saved for that date, please

i tried:

SELECT table1.date, table1.message FROM table1 WHERE table1.date = monthview1.value

Thank you

 

[tedsmith]

DataEnvironment1.rsTable1.Fields("date&quot = Text1.Text
or if you do not need a textbox then -
DataEnvironment1.rsTable1.Fields("date&quot = monthview1.value
be sure to use DataEnvironment1.rsTable1.Update when you have finished putting all the data into the new record.
If in the Dataenvironment then -
SELECT table1.date, table1.message FROM table1 WHERE table1.date = #' & monthview1.value & '#'
or use the parameter method -
SELECT table1.date, table1.message FROM table1 WHERE table1.date = ? then enter the date in the parameters tab. (See MSDN help on using the parameter I cant remember how but it is really simple)
or if in code then -
"SELECT table1.date, table1.message FROM table1 WHERE table1.date = #" & monthview1.value & "#"

 

[Guest_imported]

Thank Ted,

The

"SELECT table1.date, table1.message FROM table1 WHERE table1.date = #" & monthview1.value & "#"

line wasn't recognised.
Expected: Line number or label or statement or end of statement

Do I need to declare the line as string variable or anything? I'm using Access 2000 with Jet 4.0 - if it makes difference.

Thamks again

 

[tedsmith]

It should work if your database date field is formatted as a Date. I have used it many times with entering dates in text boxes andn using Now().
Check that the monthview1.value also is a date.
If the table1.date is a string you would use "SELECT table1.date, table1.message FROM table1 WHERE table1.date ='" & monthview1.value & "'"

How are you using the SELECT . . . string anyway?

I always avoid using Date as a name of a field as it is a reserved word and gets a bit confusing.(MyDate)

You could try Dim MyDate as Date then say MyDate=Monthview1.value then "SELECT table1.date, table1.message FROM table1 WHERE CvDate(table1.date) =#" & MyDate & "&"

 

[Guest_imported]

Thanks Ted,

I tried your code but I can't get the message to appear in the 'message' textbox. So far I have this code:

Private Sub MonthView1_DateClick(ByVal DateClicked As Date)
Dim mydate As Date
Dim sqlstring As String
mydate = MonthView1.Value
sqlstring = "SELECT table1.datex, table1.message FROM table1 WHERE CvDate(table1.datex) =#" & mydate & "&"
End Sub

...but nothing happens. I've manually added a record in the database to see if works but the date I click on won't call the message. What am I missing and what could be wrong?

Also, I have the following code.....

' Save Appointment..
Private Sub Command1_Click()
DataEnvironment1.rsCommand1.AddNew
DataEnvironment1.rsCommand1.Fields("datex") = Text2.Text
DataEnvironment1.rsCommand1.AddNew
DataEnvironment1.rsCommand1.Fields("message") = Text1.Text
DataEnvironment1.rsCommand1.Update
End Sub

......for the AddNew method but I get a run-time error:
Run-time error '3265': Item cannot be found in the collection corresponding to the requested name or ordinal.

What is wrong here?

Please help again.

 

[Guest_imported]

Anybody help on my last reply? I really need to sort this out. I can't find any info on subject and wish to finish this as soon as possible.

thanks

 

[perplexd]

On the line:
FROM table1 WHERE CvDate(table1.datex) =#" & mydate & "&"
Shouldn't the last figure be # - ie:
FROM table1 WHERE CvDate(table1.datex) =#" & mydate & "#"

On the second code you only need to .AddNew once and then update at the end...ie:

With dataenv.rs
.AddNew
!Datex = text2.text
!Message = text1.text
.Update

 

[Guest_imported]

Hello,

Thanks perplexd.

There's still a few things wrong though.

1: With the code:

With DataEnvironment.rsCommand1
.AddNew
!Datex = text2.text
!Message = text1.text
.Update
end with

..an error occurs:
Run-time erroe '3251': Object or provider is not capable of performing requested operation.

Is this something, Jet 4.0 can't do or what?


2: You gave me the correct edit for the code:

"SELECT table1.datex, table1.app FROM table1 WHERE CvDate(table1.datex) =#" & mydate & "#"

..but the message appears in the textbox as soon as the form is loaded. This is not what I want it to do. I want it to appear if the user clicks the date that the message is saved to. For example, say the user saves a message or appointment to the date 8/16/2002 - I want the message to appear only if the same date is clicked. That way the user can click any date on the monthview form and if a message is saved, it appears in the textbox. The following is the full code I've got for the dateclick event:

Private Sub MonthView1_DateClick(ByVal DateClicked As Date)
Dim sqlstring As String
Dim mydate As Date
mydate = MonthView1.Value
sqlstring = "SELECT table1.datex, table1.app FROM table1 WHERE CvDate(table1.datex) =#" & mydate & "#"
End Sub

Can you please advise on what could be wrong.

Thanks.

 

[perplexd]

With the first problem:
!Field must be the exact fieldname which you use in your database...if there are spaces surround it with square brackets: ![Field Name]

Also it will work qiucker if you pass the data in the same form as the database stores it: ie for date use:
cDate(text2.text)

Do I understand correctly what you want it to do: you only want the message to appear once a date is click, but not when initially loaded.

I'm guessing that when the form loads, the computer is selecting a date (probably today's?). This action is triggering the click event. I cannot be certain though as I have never used this control before.

If this is the case you could hide the textbox initially or otherwise set the forecolor to the same as the backcolor.

 

[Guest_imported]

Thanks again perplexd.

I'll try that out later.. But the only thing is that not only does the message appear in the textbox upon load up but when the user clicks a date nothing changes and nothing appears (I manually input a record in the database for a test: I put 12/12/2002 in the datex field and hello in the message field, but nothing happens when I click 12/12/2002 on the monthview).
Also, the datatypes for the fields are date/time for datex and memo for message - am I right in using memo for message as the textbox has it's multiline property set to true or do I need the datatype to be text??

Thanks again.

 

[perplexd]

You have to open the recordset before you can access the data...Do what is below:

Private Sub MonthView1_DateClick(ByVal DateClicked As Date)
Dim sqlstring As String
dim rs as adodb.recordset

Set rs = New ADODB.Recordset
rs.CursorType = adOpenKeyset
rs.LockType = adLockPessimistic

sqlstring = "SELECT table1.datex, table1.app FROM table1 WHERE DateField =#" & dateclicked & "#"
rs.Source = sqlstring
Set rs.ActiveConnection = con
rs.Open

'do stuff

rs.close

End Sub

if you are putting the code in the click event, dateclicked provides the date for you.

DateField needs to be changed to the name of you date field in your database.

 

[Guest_imported]

Once again, thanks for the time and effort. I'll try this out and let you know how I get on.

 

[Guest_imported]

I tried it but an error arose, about a varaible that's non-defined:

sqlstring = "SELECT table1.datex, table1.app FROM table1 WHERE DateField =#" & dateclicked & "#"
rs.Source = sqlstring
Set rs.ActiveConnection =

con

rs.Open

What is con?

Thanks.

 

[perplexd]

con is the connection - as you're using dataenv, use the connection in that.

where did the variable-defined error occur?

Another thing to try is not bothering with the hashs around the date (#)...I've got away without bothering with that before.

 

[basepointdesignz]

Sorry to tag onto someone else's thread but I have almost exactly the same problem which I can't resolve.
If anyone has the answers please let me know..

Thanks

 

[perplexd]

does the above solution not work then?

what code have you got?

where is the problem?

 

[Guest_imported]

I tried using the dataenvironment path (ie: app.path & "\appointment.mdb&quot as the con part of the code you gave me and I also tried the string; DataEnvironment1.rsAppointment but it doesn't recognise either.

As I'm still new to this, can anyone look at my code, which I plucked from various sources and stitched together hoping things would work?

Private Sub MonthView1_DateClick(ByVal DateClicked As Date)
Dim sqlstring As String
Dim rs As ADODB.Recordset
Dim con As ADODB.Connection

Set rs = New ADODB.Recordset
con.Open (App.Path & "\appointment.mdb")
rs.CursorType = adOpenKeyset
rs.LockType = adLockPessimistic
con.CursorLocation = adUseClient

Set rs = New ADODB.Recordset
Set rs.ActiveConnection = con
rs.Open

sqlstring = "SELECT table1.datex, table1.app FROM table1 WHERE DateField =#" & DateClicked & "#"
rs.Source = sqlstring

rs.Close

End Sub

Thank you

 

[Guest_imported]

When I run my program and click a date on the monthview a run-time error occurs:
Run-Time Error '91': Object Variable or With Block variable not set.
It points to the

con.CursorLocation = adUseClient

section of the code. What could be wrong?

Also, what do I need to write for this bit of code?:

rs.Open .........

I tried

rs.Open (DataEnvironment1.rsCommand1)

and I tried

rs.Open (app.path & "\support\appointment.mdb")

. No matter what I try, I get a run-time error:]
Run-Time Error '3709': Operation not allowed on an object referencing a closed or invalid connection.

I've got in such a mess and I must have such a patch-work of code (that I got from various sources) that it's hard to know where to begin.

Please someone help me!!

Thanks

 

[SemperFiDownUnda]

Run-Time Error '91': Object Variable or With Block variable not set.
if you are getting that on the line
con.CursorLocation = adUseClient
then you have
DIM con As ADODB.Connection
but you haven't instantiated the connection by
Set con = New ADODB.Connection

When you use a ADODB.Recordset Open method giving it a file name it expects the file to be from a previous ADODB.Recordset Save method.

What you want to do is open a connection to the access database then use a recordset to pull data from it. From glansing at these post I have to give you the following advice

Go get a book on ADO. Do not take offence but I'd get "ADO for Dummies" book. If you can't find that look at "VB6 for dummies" and read the chapters on ADO in there. These books are VERY good. Some of the "Dummies" books are VERY advanced but still talk in normal terms.

 

[perplexd]

rs.open has to go after rs.source line!!

I meant instead of using con use your dataenvironment name ie MyDataEnv in ever place where it says con.

Let me know how this goes. You may find it useful to get a beginners guide to VB if you are so new...you sound like you are in the position I was about 6 months ago...when I hadn't even touched programming!!