Now the tricky part. If there is no matching record, then there are some
starting values in table "B" using the vehicle number as the key. Go get
that record, whatever fields are filled in from that record can be
programatically transposed to the new form (they don't match one to one),
and
then again allow for editing and writing to table "A" using vehicle number
and date.
The first problem is does one need to copy this data over from table B
anyway? Are these really defaults that you need for from values in this
record, or is in fact information that you want to display from table B? If
you're just needing to display some of that information, then you don't want
a copy the information over, you want to use the relational ability of
ms-access to display this informaton (and, it will not take any code to
display that information from table B if you do this right.
Some other people that have used ACCESS before had no idea how to do that
with a bound form
Its rather easy to do this with a bound form, but the problem is your design
approach is somewhat wrong for ms-access, and therefore you having trouble
finding people to explain how to do this. It's not that you can not do this
in MS access, the problem is your approach is the wrong way to go about
doing this.
..>, and since I'm new to ACCESS I had no idea either. So to
get this done with I'm going with what I know. Read record from "A", if
no
record, read record from "B" and populate controls. When magic 'SAVE'
button
clicked, populate record from controls and write to "A".
dBase, dbaseIII, dbaseIV, Knowledge man, FoxPro, and *most* of the pc based
databases don't have a save button, it is implied.
Anyway:
I'm going to make the assumption that this form is based on one table. The
fact that you want to display values from the other table, or copy values
from the other table is a different issue (however it is critical you
clarify this issue).
The form you going to build is going to be bound on the first table. Can I
assume then once you've copied all the values from table B., we're adding
one record from table a, and we are editing the one reocrd in table A?
Correct?
So the first thing I would do is build your form to edit data from table A.
I suggest using the wizard to do it. This form will be built and designed on
table A (it is a few mouse clicks to do this, and takes very little time to
do this). You now have a form that will let you browse and move through the
records in table a. (the fact that you don't need to browse and edit
previous records is really moot here, we NEED to build a form that lets you
edit data from table a, and we must do that before the next step).
When we eventually get this working, will we will disable things like the
forms navigation keys, and a number of the other features that will stop the
user from editing data in this form.
We will now add two text boxes to prompt for the date, and the vehicle at
the top of this form. These will be what we call un-bound forms. You will be
able to type in your values, and this is also where we will write that code
to "read" the existing record, and, if not found, add a new record.
Do you plan to put a search button at the top of the form for the person to
press, or do you simply wanted to "search" after the second text box data is
entered? (the coding for in their case is much the same, it's just a
question do you want to put a button up their research, or simply have
things go and display after the second text box data is entered?).
I going to assume we search after the 2nd txt box data is entered.
so, we have two text boxes at the top
txtDate and txtVehicle
The code we will write thus goes in the after update event of the text box
called txtVehicle.
The code will look like:
dim strSql as string
dim rstVehicle as dao.ReocrdSet
if isnull(me.txtDate) = true then
msgbox "You must enter a date to seach for"
exit sub
end if
if isnull(me.txtVechicle) = true then
msgbox "you must enter a vehicle to search for"
exit sub
end if
' lets check if the vehicle number actually exists
' this is like a READ command in pick, we going to
' pull "one" record into a variable. This is the munch
' and crunch way of doing things
'
strSql = "select * from TableB where VehicleNum = " & me.txtVehicle
set rstVehicle = currentdb.OpenreocrdSet(strSql)
if rstVehicle.ReocrdCount = 0 then
msgbox "vehicle does not exist, try again"
exit sub
end if
' ok, loaded the vehicle, now lets search for the record. We going to use
the equivalent of much what it is like to do a TCL select command in pick)
' we're going to send the select command right into the form, and this is
legal in MS access. In ms-access, a sql select
can be sent to a reocrdset (like above, the rstVehcile code, or send that
select right into the form).
strSql = "select * from tableA where " & _
" ServiceDate = # & "format(me.txtDate,"mm/dd/yyy") & "#" & _
" and VehicleNum = " & me.txtVehicle
' in the above it's important to note that if the vehicle numbers actually
add text data type field, then you'll have to surround it and put quotes as
follows
" and VehicleNum = '" & me.txtVehicle & "'"
Ok, execute this select into the form:
me.RecordSource = strSql
' if we did not match,then reocrdCount is zero, and we add a new record
then.
if me.ReocrdSource.RecordCount = 0 then
' send form to a new reocrd
docmd.GoToRecord acDataForm,,acNewRec
' now copy values from rstVehcild into the form
me.Make = rstVehicle!Make
me.Model = rstVehcile!Model
.etc.
end if
There, that is about it. Do note that all the code I kept above was
completely what I call "air" code.
We probably should also add a save button at the bottom of form. That button
will write out the record and then clear the form for the next record.
if me.dirty = true then
me.dirty = false ' force disk write
end if
' now, clear the form
me.recordSource = "select * from tableA where id = 0"
The above will write out all the data, and the select command will return a
blank record because we've assigned a blank record to the form.
And, at this point, we like should set the focus back up at the top of the
form, and clear out the txtDate + txtVehicle controls
me.txtDate = Null
me.txtVehcile = null
me.txtDate.SetFocus
There's a number of other settings in the form's property tab that you also
should set to make this work a little bit more clean, such as removing the
navigation buttons on the form, and furthermore setting the forms record
cycle property to current reocrd.
