[AccessD] saving values

Pedro Janssen pedro at plex.nl
Tue Nov 28 16:43:47 CST 2006 

Hello Rocky,

no problem,
i am glad you wil have a look at the problem.

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: Tuesday, November 28, 2006 5:54 PM
Subject: Re: [AccessD] saving values


> Pedro:
>
> Haven't forgotten about you but I'm a little backed up this morning. 
> Stand
> by...
>
> Rocky
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Pedro Janssen
> Sent: Monday, November 27, 2006 3:05 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] saving values
>
> Hello Rocky,
>
> In this testdatabase is just want to make the cascadecombox selection 
> trying
>
> to work, for a major Fossil Database.
> The Main Table in the test is tblFossieldata (fossildata). The form
> frmFossielData is bound to that table.
> In this table you see the fields Era, Periode, Epoch, Etage and Tijd. 
> These
> are Geological Timetable fields. These fields are just textfields
>
> The table tblTijdschaal (timescale) I filled with the vlues that i want to
> use in the tblFossielData, in the fields that i mentioned before.
> Normally i would do this with a combobox for each, but the list of 
> timescale
>
> is very large, so i would simplify that by using a cascadecombo, that is
> present in frmTijdschaal.
>
> When opening a record in frmFossielData, and field Era gets the focus (in
> the real database there wil be a button click event), the unbound subform
> gets the focus.
> By using the cascade, Each field is filtered by the value of the combobox
> before.
> When selected the values in the five cascade comboboxes, i want to close 
> the
>
> frmTijdschaal en the values of Era.frmTijdschaal must be placed in the 
> field
>
> Era of frmFossielData, etc for the other four fields.
>
> 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 11:31 PM
> Subject: Re: [AccessD] saving values
>
>
>> Pedro:
>>
>> You are using my variable name lngKeyValue for the criterion in the WHERE
>> clause to limit the recordset to the one record you are looking for.  You
>> should use the value of the field from the combo box, instead.
>>
>> And I'm not clear on what you're trying to store or what the relationship
>> is
>> between the two tables.  So I can't be more precise.
>>
>> Tell me again the user process and what you're trying to accomplish.
>>
>> Regards,
>>
>> Rocky
>>
>>
>> -----Original Message-----
>> From: accessd-bounces at databaseadvisors.com
>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Pedro Janssen
>> Sent: Monday, November 27, 2006 11:51 AM
>> To: Access Developers discussion and problem solving
>> Subject: Re: [AccessD] saving values
>>
>> 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
>>>
>>
>> -- 
>> 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
>>
>
> -- 
> 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
>

More information about the AccessD mailing list