[AccessD] saving values

Pedro Janssen pedro at plex.nl
Tue Nov 28 17:10:09 CST 2006


Hello Rocky,

i have thought about the problem to.
On the unbound form i have put a textbos with the controlsource is refered 
to the ID-field in the Bound form,
so ik kan put both in the Where statement, but then the field is requeried 
after losing the focus (values are filled in),
it jumps to record one.
I will send you the zipped testdatabase again.

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