fonzie wrote:
I am very close with the setup I have now, the problem is when I add
the third table to the select statement I can only view the results in
the form, I can't add or edit of the information. Is this by design?
Does my SELECT statement need to be changed somehow?
Based on your other response to Ron, ie restricted to one form, no
subforms, here's what I would do.
I'll assume the fields identified in your join statement are prmary
keys. This is critical.
1) Design your select statement so it displays the records you want it
to. You've pretty much done that. Don't worry that the query doesn't
allow you to edit/add records.
2) Create a continuous form with a footer that displays the fields you
want. You've a fair number of fields that might lend themselves to
having a subform datasheet, but since that's out of the question, you
want to make this a continuous form so that you have a "bare footer"
showing. Maybe two or more rows in the detail section might be
necessary, perhaps with Asset tag in bold or a different colour or
something. Use text boxes to display the information for each record in
the form detail section.
3) Make sure the name property of each text box is NOT, repeat NOT the
same as the controlsource field from the query. I usually name my text
boxes txtFieldName.
4) Set the add, and delete properties on your form's property data tab
to NO. What we are going to do is set up fields in your footer and five
buttons for adding, editing and deleting records.
5) In the tag property of each text box you've just created in the
detail section, write "Display". Make sure all these text boxes are
enabled. You should also lock them - you don't want users to try and
change data in the detail section - that's what the footer secction will
be for.
6) In the footer, duplicate the controls in the detail section BUT MAKE
SURE THERE ARE *NO* CONTROLSOURCES in each text box. In other words,
you turn on your form and there's blank spaces in the controls on the
footer. Name each control as per 3, above, perhaps with Add as a
suffix, such as txtFieldNameAdd.
7) If you want to restrict users to specific data instead of the free
form a text box can potentially allow:
-a) use a combo box with a row source from one a query but with no
controlsource;
-b) use the afterupdate event to restrict what is allowed; and/or
-c) Add constraints in your table field properties with appropriate
messages.
8) For every text/combo control in the footer, mark the Tag property
"DataEntry" and disable them all.
9) Create 5 buttons in the footer. Three are labeled add, edit and
delete. Enable these buttons. Two are labeled OK and cancel and are
disabled. You can use images that clearly indicate the function of each
button.
10) Add a text box that is invisible in the footer and call it txtAddEdit.
11) What you want to be able to do is click one of the three buttons and
have the detail section freeze. Use a sub in the form something like
(All of the following is air code - no error handling will be offered here):
Sub sEnableControls(booEnable as Boolean)
'booEnable is passed as True to enable when adding/editing record
'and false to disable when cancelling or
'accepting addition/edit of a record
dim Ctl as Access.Control
'Enable dataentry controls
for each ctl in me.controls
if ctl.controltype = acTextBox or ctl.controltype = accombobox then
if ctl.tag = "dataEntry" then crl.enabled = booenable
end if
next
'Switch focus to one of the fields in the footer
'so that the following does not throw an error like
'"You can't disable the control that has the focus"
if booenable = True then
me.txtIdAdd.Setfocus
else
me.txtId.Setfocus
end if
'Do the button thing
me.btnAdd.Enabled = not booenable
me.btnEdit.Enabled = not booenable
me.btnDelete.Enabled = not booenable
me.btnOK.Enabled = not booenable
me.btnCancel.Enabled = not booenable
End Sub
When the Edit button is clicked, it loads the current values into the
controls of the footer:
Sub btnEdit_Click
'Set txtAddEdit to "Edit" so app knows what to do
'When OK is pushed.
me.txtAddEdit.Value = "Edit"
'Enable stuff
sEnableControls True
'Populate footer fields
me.txtIdAdd.Value = Me.txtId.Value
me.txtAssetTagAdd.Value = Me.txtAssetTag.Value
'etc, etc - for the add button, set these all to null,
'ie, empty
End Sub
Now, when you have done your additions, etc, set up the ok button as
follows:
Sub btnOK_CLick
dim strS as string
dim db as dao.database
dim qd as dao.querydef
If me.txtAddEdit = "Add" then
'This will mean create and execute three insert statements
'Set Up the db and querydef
set db = Access.Currentdb
'Set up a temporary querydef
set qd = db.Createquerydef("")
'Let's do tblInventory, first
strS = "INsert into tblInventory (" & vbcrlf
strs = strs & "id, AssetTag, " & vbcrlf
strs = strs & "Make, Model, serial)" & vbcrlf
strs = strs & "Select " & vbcrlf
strs = strs & me.txtIdAdd & ", """ & me.txtAssetTagAdd & """, """ &
me.txtMakeAdd & vbcrlf
strs = strs & me.txtModelAdd & ", """ & txtsERIALAdd & """"
with qd
.SQL = strs
.returnsrecords = false
.execute dbfailonerror
end with
'Repeat the above 11 lines for the other tables.
Else
'This will be fr three update statements - you can figure that out.
end if
'IF you get this far, you've been successful, hurrah!
'Enable stuff
sEnableControls False
Exit_Proc
qd.close
set qd = nothing
db.close
set db = nothing
End sub
You can fill in the blanks. I rushed this together before going home
from work. Check things carefully, it's all air code.
--
Tim
http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
