Beach Access Software
bchacc at san.rr.com
Mon Nov 27 09:39:55 CST 2006
Pedro:
Just a guess but perhaps using the WHERE clause returns no records because
the value for the PK you're using is invalid.
After the set rs = statement put
If rs.bof=true and rs.eof=true then
Msgbox "Holy Moly! There's No Records In Here!!! - " &
lngKeyValue
Exit sub
End if
To see if that's the case and what the value is of the key you're apssing.
Post the set rs = statement and let's have a look at it.
Regards,
Rocky
If it is then
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Pedro Janssen
Sent: Monday, November 27, 2006 6:55 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] saving values
Hello Rocky,
i included the Where statement, but get an eror when closing the subform
with the code:
Runtime error 3201, no current record.
When clicking debug button, rs!edit is highlited yellow.
I used help, but still do not understand it.
Pedro Janssen
----- Original Message -----
From: "Beach Access Software" <bchacc at san.rr.com>
To: "'Access Developers discussion and problem solving'"
<accessd at databaseadvisors.com>
Sent: Monday, November 27, 2006 2:59 AM
Subject: Re: [AccessD] saving values
> Pedro:
>
> You need to add the WHERE clause to your set statement:
>
> Set rs = db.OpenRecordset("Select Era, Periode, Epoch, Etage, Tijd FROM
> tblFossielData WHERE tblFossielDataPrimaryKey = " & lngKeyValue)
>
> Do you know what the primary key value is of the record you want to update
> with the values from the unbound text boxes?
>
> Rocky
>
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Pedro Janssen
> Sent: Sunday, November 26, 2006 2:31 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] saving values
>
> Hello,
>
> how would i point to the first record in the Where clausule with the ID?
> I am placing values from a unbound form, with no ID in a bound form with
> an
> ID (= FossielID).
>
>
> Please give me an idea to do this in the code below?
>
> Pedro Janssen
>
>
>
> ----- Original Message -----
> From: "Beach Access Software" <bchacc at san.rr.com>
> To: "'Access Developers discussion and problem solving'"
> <accessd at databaseadvisors.com>
> Sent: Saturday, November 25, 2006 10:30 PM
> Subject: Re: [AccessD] saving values
>
>
>> Pedro:
>>
>> It looks like you are opening the table with all of the records. So the
>> record pointer of the recor4dset is always pointing to the first record.
>> You need to either 1) use a WHERE clause in the Set rs = db.OpenRecordset
>> so
>> you get only the record you want, or 2) after opening the recordset use
>> rs.FindFirst and specify the primary key of the record you want to
>> update.
>> Then the recordset will be positioned at the record you want to update.
>> Be
>> sure to use if rs.NoMatch just in case the FindFirst doesn't find the
>> record.
>>
>> Regards,
>>
>> Rocky
>>
>>
>> -----Original Message-----
>> From: accessd-bounces at databaseadvisors.com
>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Pedro Janssen
>> Sent: Saturday, November 25, 2006 12:37 PM
>> To: Access Developers discussion and problem solving
>> Subject: Re: [AccessD] saving values
>>
>> Hello Rocky (and Others),
>>
>> the code is working fine, but only for the first record on the form.
>> When i try to use it on the second record (after closing) it jumps to
>> record
>>
>> one and overwrite's the data there.
>> Trying to put the where statement in it to state that ID = ID, it gives
>> an
>> error on record two that it expects parameter = 1.
>>
>> The code that i use now:
>>
>> Private Sub Form_Close()
>>
>> Dim db As DAO.Database
>> Dim rs As DAO.Recordset
>>
>> Set db = CurrentDb
>> Set rs = db.OpenRecordset("Select Era, Periode, Epoch, Etage, Tijd FROM
>> tblFossielData")
>>
>> rs.Edit
>> rs!Era = cboEra
>> rs!Periode = cboPeriode
>> rs!Epoch = cboEpoch
>> rs!Etage = cboEtage
>> rs!Tijd = cboTijd
>> rs.Update
>>
>> rs.Close
>> db.Close
>>
>> Set rs = Nothing
>> Set db = Nothing
>>
>> Me.Requery
>>
>> End Sub
>>
>> What can i do about this?
>>
>> Pedro Janssen
>>
>>
>>
>>
>>
>>
>> ----- Original Message -----
>> From: "Beach Access Software" <bchacc at san.rr.com>
>> To: "'Access Developers discussion and problem solving'"
>> <accessd at databaseadvisors.com>
>> Sent: Friday, November 24, 2006 9:09 PM
>> Subject: Re: [AccessD] saving values
>>
>>
>>> Pedro:
>>>
>>> Just off the top and untested:
>>>
>>> Dim db as DAO.Database
>>> Dim rs as DAO.Recordset
>>>
>>> Set db = CurrentDb
>>> Set rs = db.OpenRecordset("Select fldOne, fldTwo, fldThree FROM
>>> tblMyTable
>>> WHERE fldPK = " & lngPrimaryKey)
>>>
>>> ' Note: You could use any criteria you want in the WHERE clause; doesn't
>>> have to be the primary key.
>>>
>>> rs.Edit
>>> rs!fldOne = cboComboBox1
>>> rs!fldTwo = cboComboBox2
>>> rs!fldThree = cboComboBox3
>>> rs.Update
>>>
>>> rs.Close
>>> db.Close
>>>
>>> set rs = Nothing
>>> set db = Nothing
>>>
>>> Me.Requery (assuming you want the values to show on the bound form.
>>>
>>>
>>> Or here's another approach which I think should work but I haven't
>>> tested:
>>>
>>> Set db = CurrentDb
>>> Db.Execute "UPDATE tblMyTable SET tblMyTable.fldOne = '" & cboComboBox1
>>> &
>>> "', tblMyTable.fldTwo= '" & cboComboBox2 & "', tblMyTable.fldThree = '"
>>> &
>>> cboComboBox3 & "';"
>>>
>>> Note that I have assumes that the values are string values and so need
>>> to
>>> have preceding and following apostrophes. If the values are numeric
>>> then
>>> they won't need the '.
>>>
>>> I'm sure someone else looking at this code will make the appropriate
>>> corrections.
>>>
>>> Regards,
>>>
>>> Rocky
>>>
>>>
>>> -----Original Message-----
>>> From: accessd-bounces at databaseadvisors.com
>>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Pedro Janssen
>>> Sent: Thursday, November 23, 2006 3:35 PM
>>> To: Access Developers discussion and problem solving
>>> Subject: Re: [AccessD] saving values
>>>
>>> Hello Rocky,
>>>
>>> could you give me an example of that, preferably in a example database.
>>> I have tried several things until now but nothing is working perfect.
>>>
>>> Pedro Janssen
>>>
>>>
>>> ----- Original Message -----
>>> From: "Beach Access Software" <bchacc at san.rr.com>
>>> To: "'Access Developers discussion and problem solving'"
>>> <accessd at databaseadvisors.com>
>>> Sent: Friday, November 24, 2006 12:07 AM
>>> Subject: Re: [AccessD] saving values
>>>
>>>
>>>> I'd use Dao, open the record in question, push the value in, and
>>>> requery
>>>> the
>>>> form.
>>>>
>>>> Rocky
>>>>
>>>>
>>>> -----Original Message-----
>>>> From: accessd-bounces at databaseadvisors.com
>>>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Pedro
>>>> Janssen
>>>> Sent: 23 November 2006 13:32
>>>> To: AccessD
>>>> Subject: [AccessD] saving values
>>>>
>>>> Hello Group,
>>>>
>>>> i still have troubles saving values from unbound comboxes (cascade) in
>>>> table
>>>> fields.
>>>>
>>>> What is the best way to do this.
>>>>
>>>> Pedro Janssen
>>>> --
>>>> AccessD mailing list
>>>> AccessD at databaseadvisors.com
>>>> http://databaseadvisors.com/mailman/listinfo/accessd
>>>> Website: http://www.databaseadvisors.com
>>>>
>>>> --
>>>> No virus found in this incoming message.
>>>> Checked by AVG Free Edition.
>>>> Version: 7.5.430 / Virus Database: 268.14.14/547 - Release Date:
>>>> 22/11/2006
>>>> 17:41
>>>>
>>>>
>>>> --
>>>> AccessD mailing list
>>>> AccessD at databaseadvisors.com
>>>> http://databaseadvisors.com/mailman/listinfo/accessd
>>>> Website: http://www.databaseadvisors.com
>>>>
>>>
>>> --
>>> AccessD mailing list
>>> AccessD at databaseadvisors.com
>>> http://databaseadvisors.com/mailman/listinfo/accessd
>>> Website: http://www.databaseadvisors.com
>>>
>>> --
>>> No virus found in this incoming message.
>>> Checked by AVG Free Edition.
>>> Version: 7.5.430 / Virus Database: 268.14.14/547 - Release Date:
>>> 22/11/2006
>>> 17:41
>>>
>>>
>>> --
>>> AccessD mailing list
>>> AccessD at databaseadvisors.com
>>> http://databaseadvisors.com/mailman/listinfo/accessd
>>> Website: http://www.databaseadvisors.com
>>>
>>
>> --
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>>
>> --
>> No virus found in this incoming message.
>> Checked by AVG Free Edition.
>> Version: 7.5.430 / Virus Database: 268.14.15/550 - Release Date:
>> 11/24/2006
>> 5:20 PM
>>
>>
>> --
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.5.430 / Virus Database: 268.14.16/551 - Release Date:
> 11/25/2006
> 10:55 AM
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.430 / Virus Database: 268.14.16/552 - Release Date: 11/26/2006
11:30 AM