Full Version: Updating Bound Form Control With Unbound Text Field Data
UtterAccess Forums > Microsoft® Access > Access Forms
super8jeff
Hello,
I'd like to add concatenated data to a bound text box upon submit, or whenever, as long as it saves in the table with the corresponding record. I have a unbound text box called eventCodeGrabber which holds the concatenated string and I'd like to have it saved into my bound text box eventCode in the tblEvents.
I have tried
CODE
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim varTextData As String
    varTextData = eventCodeGrabber
    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblEvents", dbOpenDynaset)
    rst.Edit
    rst!eventCode = varTextData
    rst.Update
    
    Me!eventCode = ""
DoCmd.Close acForm, "frmEvents", acSaveYes
  End If
Else
    varTextData = eventCodeGrabber
    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblEvents", dbOpenDynaset)
    rst.Edit
    rst!eventCode = varTextData
    rst.Update
    
    Me!eventCode = ""
DoCmd.Close acForm, "frmEvents", acSaveYes

Which does add the data but to the first record no the record that I had open.
I appreciate your guidance,
Jeff
Bob G
nowhere in your code do you go to a specific record in your recordset. So, unless the recordset only has one record it will only update the record it is on.
You may want to change your code a bit. something like this air code
I use chr(34) because i can never get the proper number of quotes.
your vartextdata would have to exist already and then you can update it to something else
CODE
Set rst = db.OpenRecordset("select * from tblEvents where eventCode = " & chr(34) &  varTextData & chr(34), dbOpenDynaset, dbseechanges)
Jeff B.
<Jeff> (nice name, by the way!)
may be reading too much into your description. If so, disregard ...
It sounds like you are concatenating data (i.e., putting together multiple facts) and storing that concatenated value in a field in a table. If that's an accurate paraphrase, please describe why having all those pieces mushed together in a field is desireable (i.e., what will you/your users be able to do with that?).
super8jeff
Hey Jeff B... Believe it our not I am a Jeff B too! I am concatenating the eventType, eventDate and eventLocation so that on a report that gives event numbers users can just choose this event code. I planned on creating a combobox where users could choose something like REIJACK24102012 (for Reintegration(eventType), Jacksonville(Location) and Date of event) instead of having to remember and input each field to bounce off a query. My eventCodeGrabber (unbound textbox) holds = Left([tblEvent].[eventType],3) & Left([tblEvent].[eventLocation],4) & Format([tblEvent].[eventDate],"ddmmyyyy")
So, if there is an easier way of submitting this eventCode that is generated when the record is created, I am all ears. I have tried putting Me.eventCodeGrabber = Me.eventCode(bound text) and vice versa into the forms after Update as well as the On dirty of both the bound and unbound text boxes but no dice.
I appreciate your time!
Respectfully,
Jeff
tina t
you can do that, without having to put the concatenated values into an "extra" field in the table. try setting the combobox properties as follows:
owSourceType: Table/Query
RowSource: SELECT eventType & eventDate & eventLocation As EVENTCODE, eventType, eventDate, eventLocation FROM MyTableName;
ColumnCount: 4
ColumnWidths: 1";0";0";0"
BoundColumn: 1
so the combobox droplist will show only the "event code", but when the user chooses a code, the data from those three fields will be available for reference.
then add three unbound textbox controls to the form, setting their Visible property to No. set their control names and ControlSource properties as follows:
Name: txtEventType
ControlSource: =[MyComboboxName].[Column](1)
Name: txtEventDate
ControlSource: =[MyComboboxName].[Column](2)
Name: txtEventLocation
ControlSource: =[MyComboboxName].[Column](3)
the unbound textbox controls can be referenced in a query, as
WHERE eventType = [Forms]![MyFormName]![txtEventType] And eventDate = [Forms]![MyFormName]![txtEventDate] And eventLocation = [Forms]![MyFormName]![txtEventLocation]
and there you have it - the user has a single code to choose from a form's combobox droplist, and the system has individual field values to query against.
hth
tina
super8jeff
Tina!
Sweet! That answers the back end part when I create my frmAfterEventNumbers. I am still having trouble with getting the eventCode into the tblEvent from my frmEvents?
Bob G
please see my post above
tina t
that was kind of the point, hon. you don't need to store the concatenated value in the table - which violates relational design principles - just concatenate the values at runtime, whenever and wherever you need to use them together.
th
tina
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.