Pedro Janssen
pedro at plex.nl
Mon Nov 27 13:50:37 CST 2006
Hello Rocky,
the message that is get back is "Holy Moly! There's No Records In Here!!! -
0
That is strange, because in record two the FossielID (Primary Key of the
table) value is 2 (auto Number field).
Ik could sent you the small test.mdb,
maybe you have seen the problem then in a minute?
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 4:39 PM
Subject: Re: [AccessD] saving values
> 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
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>