Re: Records not adding...Please help!

Tech-Archive recommends: Speed Up your PC by fixing your registry

Responses inline.

"Yula" <Yula@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:5E23E448-3E71-4DE9-B077-89817C845721@xxxxxxxxxxxxxxxx
Bruce thank you for all your advise so far, but here is the problem. I
have
been working on this project for a while and I really need to come up with
a
form for people to fill out that will store the info that they fill in. I
don't have the luxury any more to learn more termanology, I have to show
something for all the time I out in. I leaned access from scratch and the
posts that i found here... and I am sure I know maybe 3% of what's there,
but
that's not excuse for not getting this project done.

A great deal can be accomplished with just a fraction of the capabilities of
Access, but a solid design is essential to using Access properly. Without
knowing more about how your database is structured or the real-world
situation it is trying to addess I may not be able to offer many useful
suggestions. Much can be accomplished with Access, but there is a
substantial learning curve. You may not have enough knowledge of the
product to get the particular job done. By analogy, having a learner's
permit, and being familiar with the basics of putting a car into gear and
steering it around the neighborhood, does not qualify you to take part in a
road rally. It's not an excuse, it's a fact.


To answer your questions: I ran the compile in VBA and there was one
problem, I took care of it. When I say Control source = master Item
#=forms!FrmJob1ID.Column(7), I mean by that the control source in the
properties *** for that text box is called master item #.

For what text box? What is Forms!frmJob1ID.Column(7)?

Also, I don't think I would need a "contact" table to be updated b/c the
user is selecting from a given list of operators. When they select the
operator from the combo box, other text boxes are automatically populated.

The Contacts table was an example: "If you have a Contacts table ...". It
is a familiar situation, so I had hoped it would be useful as an example.


Ex. Combo box Operator. Row souce in properties is called operator query
Column 1 shows: Bob, John, Joe, Mike

What is the source for the operator query? Is there an Employee table or
something of the sort?


Machine they work on text box: autopopulates 12345 (based on Operator
query
column 2)

Each operator works on just one machine? Do you mean they operate the
machine, or is this a shop that repairs machines? "Work on" is a vague
term. In any case, "machine they work on" is not an attribute of an
operator, so the information should be stored in a separate table. If that
is already the case, and the operator query assembles information from
several tables, the question remains whether you need to store the
information about machine and shift, or just link to it.


Shift they work, text box autopopulates 1 (based on operator query column
3)

That's pretty much the set up for this form. When the user selects
something
from the combo box(based on row sources and the text boxes based on
control
sources), other text boxes autopopulate. I hope this makes sense.

I'm sorry to have to tell you it does not make much sense to me. I am
unfamiliar either with the real-world business situation you are trying to
address or the structure of your database. If you can post a description of
the tables and relationships it may be possible to come up with something
more definite.

"BruceM" wrote:

With the VBA editor open, click Debug > Compile ___ to compile the code.
If
you have created a situation such as attempting to requery a field
(rather
than a control), it will highlight the line of code. It also guards
against
typographical errors, and quite a few other things that will prevent the
code from running.
I am not quite sure what to make of something like:
control source = Master Item2 = Forms!FrmJob1!ID.Column(7)
Is MasterItem2 a combo box? If so, Forms!FrmJob1!ID.Column(7) cannot be
its
control source. The Control Source is the field to which a control is
bound. If you have a Contacts table with fields for FirstName, LastName,
etc., and you create a form bound to the Contacts table for entering and
editing contact information, then information typed into a text box with
its
control source set to FirstName will store that information in the
FirstName
field of the Contacts table.
To elaborate a bit, open a form in design view. Click View > Properties
(or
double click the small square at the very top left of the form window).
You
will see a place for the Record Source. If you select a table or a query
bound to the table as the Record Source, the form is a bound form. That
is,
it is bound to that record source (table or query). The form becomes a
sort
of portal to the table.
Next, the controls. A control is a combo box, text box, label, line, or
just about anything else on the form. Some controls, such as text boxes
and
combo boxes, may be bound to fields in the form's Record Source. That
is, a
field from the form's Record Source is selected as the control's Control
Source. Information entered into the bound control (or selected from a
combo box list, which accomplishes the same thing as typing into a text
box)
will be stored in that field in the form's Record Source.
Back to the code you have provided, this cannot do anything I can see:
Control Source=PS Item 2= " " (8)
Another thing I found puzzling was that you said the info needs to be
saved
to a new table. Why? It is possible to create a table through code and
store information there. I couldn't say exactly how to go about that,
but I
doubt very much that it is what you need to do.
I think a greater understanding of some of the terminology would be of
help.
A good place to start is here:
http://allenbrowne.com/tips.html
In particular, look at the Table Design links in the Tips for Casual
Users.
On the right side of the page, toward the bottom of the list, is a Links
link. That will take you to quite an extensive list of Access links for
users of all levels.

"Yula" <Yula@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B0C56AB9-3902-4FA7-A02D-FA1C02B774D3@xxxxxxxxxxxxxxxx
Below are my combo boxes:

Combo Category = Row Source = Select Query
Combo Job = Row Source = Query 5 (Main Query)

Cat ID, Cat code, Job Name, Master item, PS item, Master Desc, Price,
Master
item 2, ps item2, price2, mat2, job cc, contact

Combo ID = row source = query 5, event procedure after update

text box = count

postage = row source = select query postage

tab 2 - materials

control source = Master Item = Forms!FrmJob1!ID.Column(3)
Control Source=PS Item = " " (4)
Control Source = Desc = " " (5)
Control Source = Price = " " (6)

control source = Master Item2 = Forms!FrmJob1!ID.Column(7)
Control Source=PS Item 2= " " (8)
Control Source = Desc 2= " " (10)
Control Source = Price 2= " " (9)

Summary
Control Source Material A =
forms!FrmJob1ID.Column(6)*Forms!frmjob1!count
Control Source Material b =
forms!FrmJob1ID.Column(9)*Forms!frmjob1!count
Control Source OH = Forms!frmjob1!count*.1234
Control Source Postage =
Forms!frmjob1!count*Forms!FrmJob1!Postage.column1(1)

I need the user to fill in the info and all the info needs to be saved
to
a
new table. Bruce, I am not sure what you mean by compile the code? Do
you
mean run? everything works when I click out of design view. I just
can't
add
any records or save it anywhere.

"BruceM" wrote:

Are the combo boxes and text boxes that are based on queries bound to
fields
in the form's record source query?
Saving the record After Update will not do anything I can see, since
updating means the record has been saved. You would be saving it
after
it
has been saved. However, if you wish to force a save during record
creation
(for instance, if there is validation code in Before Update you may
wish
to
force the Before Update event to run) you can just use Me.Dirty =
False
instead of that MenuItem stuff.
Are you able to compile the code?

"Yula" <Yula@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:5236AB86-43DC-4333-8AEF-EAEEF11065C0@xxxxxxxxxxxxxxxx
I created a standard form and than I added a lot of combo boxes and
text
boxes that are based on queries. Also, below is the forms after
update
procedure. I have combo boxes based on one another.

I think the problem is that the form needs some kind of a procedure
to
save
to the recordsource table. I think there is a diconnect between the
controls
being selected on the form and the table where I want them to be
saved.
I
am
just not sure what to do about it. Should I create a procedure to
save
afterupdate to the table which is my recordsource



Private Sub Category_AfterUpdate()
Me.Job = Null
Me.Job.Requery
Me.Job = Me.Job.ItemData(0)
End Sub







Private Sub JES_AfterUpdate()
Me.JES = Null
Me.JES.Requery
Me.JES = Me.JES.ItemData(0)
End Sub


Private Sub Form_Current()
If Me.NewRecord Then
Me!Inserter.SetFocus
End If


End Sub

Private Sub ID_AfterUpdate()

End Sub

Private Sub ID_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub Job_AfterUpdate()
Me.ID = Null
Me.ID.Requery
Me.ID = Me.ID.ItemData(0)
End Sub


Private Sub Command230_Click()
On Error GoTo Err_Command230_Click


DoCmd.GoToRecord , , acNext

Exit_Command230_Click:
Exit Sub

Err_Command230_Click:
MsgBox Err.Description
Resume Exit_Command230_Click

End Sub
Private Sub Command231_Click()
On Error GoTo Err_Command231_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70

Exit_Command231_Click:
Exit Sub

Err_Command231_Click:
MsgBox Err.Description
Resume Exit_Command231_Click

End Sub

"Maurice" wrote:

Yula,

The SQL-statement looks straight forward. Have you created a
standard
form
and changed nothing. In that case you should be able to enter data.
Is
there
a subform on the form? If you look in design of the form look at
the
properties of the form to see if the allow additions property is
set
to
yes.
I assume there's no code behind the form what could be 'bugging'
your
form?
--
Maurice Ausum


"Yula" wrote:

Thank you for responding: Below is the SQL for the form... Again,
it
almost
seems that the recordsource for the form is in no way tied to the
selections
in the controls.

SELECT Main_Table.Inserter, Main_Table.Date, Main_Table.Meter,
Main_Table.Shift, Main_Table.Category, Main_Table.Job,
Main_Table.ID,
Main_Table.Count, Main_Table.Postage, Main_Table.MasterItem1,
Main_Table.PSItem1, Main_Table.Description1, Main_Table.Price1,
Main_Table.MasterItem2, Main_Table.PSItem2,
Main_Table.Description2,
Main_Table.Price2, Main_Table.CostCenter, Main_Table.Contact,
Main_Table.OH,
Main_Table.Post, Main_Table.Total
FROM Main_Table;




"Maurice" wrote:

Why not show us the sql statement? It might be that your
comboboxes
are bound
while they should be unbound because the come from different
tables...
--
Maurice Ausum


"Yula" wrote:

I have read thru all the posts and I can't figure out what I
am
doing wrong.
I am not at all an expert in Access and everything I have
done
so
far was
based on help I received while posting here. I finally
figured
out
how to
make one combo box base on another, I have come to a complete
halt
with this
form. I can't add records, the whole point of this project. I
looked at all
the properties and everything is enabled. I copied the SQL
into
a
new query
and there I was able to add records. I am sure, than it's
something
with the
form. I just don't have enough knowledge to figure out how to
fix
this.

Could it be that I have a lot of combo boxes based on
queries??

I greatly appreciate any help I could get!
Yula









.

Quantcast