Now a new problem...

[insomnai]

Ok, I have my form designed, with modifications being added all the time.... What I would like to do now is pass information gathered from a series of named unbound textboxes and a selected entry in a listbox to a table. This table will then provide data to a subform in data view so I can edit things as I go.

If this has already been asked then I apologise but searches on here have provided no results.

Many regards

Craig

[OBP]

Craig, when using Unbound Fields you have to use VBA and a Recordset to send the data to a table.
To assist you further we would need to know the Names of the Fields on the Form and the Names of the Corresponding Fields in the Table you want the data to go in.

Why aren't you just putting the data straight in to the table?

[insomnai]

Well, to be honest, it was a lack of planning on my part. The main form is ever evolving and as such, I've left everything unbound for the time being. And, of course, I just want to know how to do it!

I can see that some of the information would be better and can be bound, but the table that this information is going to be passed to is going to be doing a lot of stuff and showing as a continuous form (subform) or datasheet.

Some of the information that is going to be passed on is from various sources too so one table being the receiver rather than the giver seems better (in my current understanding...)

Many thanks

Craig / Insomnai

note:

form is called frmNewBookings
textboxes are txtName, txtTelephone,
listboxes that contain queried information is lstTrvFrom and lstTrvTo
checkbox for chkWaitReturn
combobox for driver selection cboAvailDriver (queried depending on if on duty)

from what i can see, txtName and txtTelephone, chkWaitReturn can be bound to the form but the listboxes and combobox will have to be added on manually....

any help is greatfully received...

[OBP]

I will post you some code tomorrow.

[OBP]

Craig, they can all be bound to the table, it doesn't matter if data is selected in a combo or typed in, as long as it appears in a field it can be bound.
However I will show you how to enter the data in to the table using a recordset, what is the table name?
I have assumed that your Table Names are basically the same as your Form Names.

Dim rstable As Object
Set rstable = CurrentDb.OpenRecordset("tablename") ' where tablename is the actual name of your table
With rstable
.AddNew
!Name = Me.txtName
!Telephone = Me.txtTelephone
if me.chkWaitReturn = -1 then !WaitReturn = -1
!AvailDriver = Me.cboAvailDriver
!From = Me.lstTrvFrom
!To = Me.lstTrvTo
.Update
.Bookmark = .LastModified
End With
rstable.Close
Set rstable = Nothing

Have you considered how you will Modify any Saved data?

[insomnai]

OBP thank you for that input, it's greatfully received my friend. I do have a a couple of questions though if you could explain the use of this code...

DIM rstable As Object - what does this actually do?

set rstable= ~ I presume this lot sets the database.recordset "table to be used" into some kind of locked place for reference?

.addnew seems pretty self explanatory

!name i take it is a field name in the table where this is going to be stored? and me.txtname, what is the me. bit for? I can see that it is reference to the current form txtName object but what does the me.bit mean?

if me.checkwaitreturn // why is this an if statement and not the same as the above example of !name etc.

.update what does this do?
.bookmark i've seen this used before, my limited understanding is that it puts the current recordset back on the screen, is this right? Ideally I would click the btnAddBooking button and the data is passed to the table, and a new blank set of boxes for fresh entry. Of course, I'm no expert :-)

Yes I have considered the data once it's in the table.

The fields here are going to be locked and the 'add' button that your code is meant for will essentially clear the screen and timestamp the record as the time of call. This info will be shown in a subform in dataset format that I can select. Then, once I've selected a particular record, then btnDispatch will timestamp Dispatched, btnMobile will timestamp Mobile and btnCleared will timestamp the Cleared fields. This will be done from the computers system time and can't be edited.

This make any sense? lol. This is a project and a half for me.

Many thanks

Oh lastly, you said they can all be bound to a table, even if some of the information is coming from another table?

Craig

[OBP]

Dim is an old BASIC command that puts aside Memroy for storing information and you had to tell it what type of info it was going to be. VBA still uses it.
Set rstable actually loads in to memory the Table as a Recordset.
!name is a Table Field called name, yours may not be.
It doesn't have to be like it, as it is it only sets the Checkbox if the Checkbox has been Ticked.
.Update actually does the business of Addnew record and puts it in the table according to the instructions that you have just given it.
The .bookmark holds the record number of the new record just created.

It would do as you say except it wouldn't clear the unbound fields on the form, you would have to use more VBA to do that.

Yes it makes sense.

Yes you can take data from more than one table, but you do not put the first table's data in to the second table, that is a Relational Database taboo, you just refer to it using it's ID number.

[insomnai]

Oddly enough, I'm no programmer but this actually makes sense. I might look further into VB!

Yeah I think I understand the relational tables too. Not duplicating information between tables when you can have a simple number that match between them and another which has a matching simple number and the actual information.

I think, lol.

Cheers OBP

Edit: just tried this but i'm getting a compile error....

table name is correct but it's at the very first hurdle.

!Name = Me.txtCustName

Name is field name in tblNewBookings
txtCustName is unbound textbox on form frmNewBookings

I put the comment tag on this in the VB window and continued the debugger but it all worked ok after that. Just this one causing the trouble. Also, it was noted that if I manually type !Name = Me.txtCustName a list usually appears in the predictive box, and txtCustName isn't in there.... Could there be something wrong with the way I've set the box up?

Cheers

Second edit: I changed the name of the txtCustName box to txtName and the reference to it in the VB window suddenly turned up when I did the Me.txtName. Now works beautifully. Many thanks.

I'm also going to take a look into moving all these into bound boxes but the exercise was a good learning expedition!

:-)

Third edit, i've managed to add a bit on the end of your code to blank the fields and set the focus back to the first box. Seems to be working fine so far!!