[AccessD] saving values

Beach Access Software bchacc at san.rr.com
Tue Nov 28 10:54:01 CST 2006 

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

More information about the AccessD mailing list