Please Help!? Form to Table Data - No Entries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Morning All,
Within a form (Job Number) I have a command button that opens another form
(Parts Required) used to list Parts that are required for a certain Job. The
two forms are related with respect to "Job No". So when the form opens I use:

Job No = Forms![Job Number]![Job No]

to specify the job number for the part. Problem is that the Job Number is
not being logged into the original Parts Required table and is therefore
entering these parts for all Job Numbers.
Hope that makes sense, Any suggestions would be much appreciated.
Cheers
 
A. Smart said:
Within a form (Job Number) I have a command button that opens another form
(Parts Required) used to list Parts that are required for a certain Job. The
two forms are related with respect to "Job No". So when the form opens I use:

Job No = Forms![Job Number]![Job No]

to specify the job number for the part. Problem is that the Job Number is
not being logged into the original Parts Required table and is therefore
entering these parts for all Job Numbers.
Click to expand...


The above line of code is invalid because the Job No
field/control has a space in the name and you did not use
square brackets around it:

[Job No] = Forms![Job Number]![Job No]

BUT, you never said where you put that line of code, so, all
by itself, it could be the right or wrong thing to do.

It's also important to see how the [Job Number] form opens
the parts form. I suspect that there may be some things to
straighten out with this aspect of your question.

If you need further assistance, please post the button's
Click event, the relevant procedure in the parts form along
with a some more details about how your scenario is supposed
to work.
 
Ok, sorry. The code I have written is for the field [Job No] in the Parts
Required form. This is to match the Job Nos. in both forms, and was written
correctly as u rightly said with square brackets.

Heres the code for the click event to open the Parts Required form (I think
I see some criteria missing already):

Private Sub Command45_Click()
On Error GoTo Err_Command45_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Parts Required"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command45_Click:
Exit Sub

Err_Command45_Click:
MsgBox Err.Description
Resume Exit_Command45_Click

End Sub

Whats supposed to happen is that you open the form and enter parts, which
are individual recorded. So that you have a number of records associated with
the Job No.
Cheers
--
Regards

Ashley Smart


Marshall Barton said:
A. Smart said:
Within a form (Job Number) I have a command button that opens another form
(Parts Required) used to list Parts that are required for a certain Job. The
two forms are related with respect to "Job No". So when the form opens I use:

Job No = Forms![Job Number]![Job No]

to specify the job number for the part. Problem is that the Job Number is
not being logged into the original Parts Required table and is therefore
entering these parts for all Job Numbers.
Click to expand...


The above line of code is invalid because the Job No
field/control has a space in the name and you did not use
square brackets around it:

[Job No] = Forms![Job Number]![Job No]

BUT, you never said where you put that line of code, so, all
by itself, it could be the right or wrong thing to do.

It's also important to see how the [Job Number] form opens
the parts form. I suspect that there may be some things to
straighten out with this aspect of your question.

If you need further assistance, please post the button's
Click event, the relevant procedure in the parts form along
with a some more details about how your scenario is supposed
to work.
Click to expand...
 
A. Smart said:
Ok, sorry. The code I have written is for the field [Job No] in the Parts
Required form. This is to match the Job Nos. in both forms, and was written
correctly as u rightly said with square brackets.

Heres the code for the click event to open the Parts Required form (I think
I see some criteria missing already):

Private Sub Command45_Click()
On Error GoTo Err_Command45_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Parts Required"
DoCmd.OpenForm stDocName, , , stLinkCriteria [snip]
Whats supposed to happen is that you open the form and enter parts, which
are individual recorded. So that you have a number of records associated with
the Job No.
Click to expand...


Right! The missing statement is:

stLinkCriteria = "[Job No] = " & [Job No]

Then the line you posted earlier would be in the parts
form's BeforeInsert event.
 
Still not working Marsh!! All that u've said makes absolute sense I just
can't get it to work!
I am not getting the corresponding Job No in the Parts Required form I just
get a 0. Even when I manually enter the Job No in this form it doesn't enter
the data into the Parts Required table. Silly question but, should Job No in
the Parts Required form still have the control source of Job No in the Parts
Required table.

Cheers
--
Regards

Ashley Smart


Marshall Barton said:
A. Smart said:
Ok, sorry. The code I have written is for the field [Job No] in the Parts
Required form. This is to match the Job Nos. in both forms, and was written
correctly as u rightly said with square brackets.

Heres the code for the click event to open the Parts Required form (I think
I see some criteria missing already):

Private Sub Command45_Click()
On Error GoTo Err_Command45_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Parts Required"
DoCmd.OpenForm stDocName, , , stLinkCriteria [snip]
Whats supposed to happen is that you open the form and enter parts, which
are individual recorded. So that you have a number of records associated with
the Job No.
Click to expand...


Right! The missing statement is:

stLinkCriteria = "[Job No] = " & [Job No]

Then the line you posted earlier would be in the parts
form's BeforeInsert event.
Click to expand...
 
A. Smart said:
Still not working Marsh!! All that u've said makes absolute sense I just
can't get it to work!
I am not getting the corresponding Job No in the Parts Required form I just
get a 0. Even when I manually enter the Job No in this form it doesn't enter
the data into the Parts Required table. Silly question but, should Job No in
the Parts Required form still have the control source of Job No in the Parts
Required table.
Click to expand...


Absolutely. The Job No field in the parts table must be
bound to the Job No text box on the parts form. You didn't
say that it is or isn't that way now, but the symptoms you
described sure make it sound as if the job no text box has a
blank control source.
 
A. Smart said:
Yes it is bound thats why I thought it was a silly question to ask. Any other
ideas. ;)
Click to expand...


The only other thing I can think of is that the line of
code:
Me.[Job No] = Forms![Job Number]![Job No]

is not in the right place. Earlier, I said that it should
be in the parts form's BeforeInsert event, is that where you
put it? Don't forget that the BeforeInsert event only fires
when you enter a keystroke somewhere on a new record.

The jobs form is still open while you are entering data in
the parts form, right?

I am worried when you said the value is not saved even when
entering the job number manually. This clearly implies that
the text box is not bound to the form's recordsource field.
I don't what else to say about this except to double check
that the control and field names are really what they are
supposed to be.
 
Alls fine and dandy its finally working!!!!!!!!!!!!!!!!!! I used VBA Code in
the BeforeInsert field rather then using Expressions.
Thanx for all your help!
--
Regards

Ashley Smart


Marshall Barton said:
A. Smart said:
Yes it is bound thats why I thought it was a silly question to ask. Any other
ideas. ;)
Click to expand...


The only other thing I can think of is that the line of
code:
Me.[Job No] = Forms![Job Number]![Job No]

is not in the right place. Earlier, I said that it should
be in the parts form's BeforeInsert event, is that where you
put it? Don't forget that the BeforeInsert event only fires
when you enter a keystroke somewhere on a new record.

The jobs form is still open while you are entering data in
the parts form, right?

I am worried when you said the value is not saved even when
entering the job number manually. This clearly implies that
the text box is not bound to the form's recordsource field.
I don't what else to say about this except to double check
that the control and field names are really what they are
supposed to be.
Click to expand...
 
Back
Top