Search box to find parts to add to job instead of a combo box (2 Viewers)

[andymartin3186]

When you press the button - triggering the On Click event obtain the PartID, and the JOBID. In your code construct a SQL INSERT statement, which you then execute:
INSERT INTO table_name (column1, column2, column3) VALUES ('value1', 'value2', 'value3');

so it might be

intPartID = me.PartNo
intJobID = me.JobID

strsql = "INSERT INTO JobParts (PartID, JobID) Values ( " & intPartID & ", " & intJobID &)
debug.print strsql

Docmd.runsql strsql

you need to dim the variables, and you should use Debug.print strsql to check it is properly formed / valid before executing then comment it out
And when it is working to your satisfaction you may then want to turn off the warning message confirming are you sure you want to insert the record, immediately before the Docmd and turn warnings on again immediately after.

Many thanks, this worked brilliantly. The code I used at the end was

Dim intPartID As Integer
Dim intJobID As Integer

intPartID = Me.PartID
intJobID = Forms![Job Form]![JobNumber]

strsql = "INSERT INTO JobParts (PartID, JobID) Values ( " & intPartID & ", " & intJobID & ")"
Debug.Print strsql

DoCmd.RunSQL strsql

Forms![Job Form]![Job Subform].Requery




End Sub

 

[GaP42]

Many thanks, this worked brilliantly. The code I used at the end was

Dim intPartID As Integer
Dim intJobID As Integer

intPartID = Me.PartID
intJobID = Forms![Job Form]![JobNumber]

strsql = "INSERT INTO JobParts (PartID, JobID) Values ( " & intPartID & ", " & intJobID & ")"
Debug.Print strsql

DoCmd.RunSQL strsql

Forms![Job Form]![Job Subform].Requery




End Sub

Good to hear - good luck. Also consider how to get quantity entered for each Job-Part. Set to 1 as default.

 

[June7]

RunSQL will trigger warning popup. Avoid that by turning Warnings off then back on after. Or use CurrentDb.Execute instead of DoCmd.RunSQL.

 

[andymartin3186]

RunSQL will trigger warning popup. Avoid that by turning Warnings off then back on after. Or use CurrentDb.Execute instead of DoCmd.RunSQL.

Hi June
That was really helpful ive amended this and prefer it without warnings.
I might do the same when deleting a line but may be useful to prevent accidental deletes

 

[andymartin3186]

Just to show you how it now looks, I've added tab pages to make it more neat so the components section of a job is on its own page. Working fantastic. Really grateful for all your help.

 

[andymartin3186]

Quick question and probably one that is easily fixed
Parts total at the bottom is a calculation from sum of line totals.
If no parts are added to a job this box is empty.
A box on the main screen also refers to this and gives an #error until a part is added.
How do I change the error to £0.00?

 

[Gasman]

Use Null() function?

 

[andymartin3186]

This is the code I put into the box for the line total. Does the code need to go into the other box which is showing the error? Currently this box just points to the other control

 

[Gasman]

The expression needs to go wherever your error is.
Think about it.

 

[andymartin3186]

Why doesnt the parts total box display an error but the box which points to parts total does?

The other box just has =[Job Subform].[Form]![PartsTotal] and this is the one which shows #error. I don't know how to add an IsNull statement to this as its just referencing the other box?

 

[June7]

There is no Null() function. There are Nz() and IsNull() and Is Null.

Testing for Null in main form textbox won't work because there is no Null, there is an error and the error will just propagate and textbox shows #Size! error.

I did a test and when there are no records in subform, main form textbox shows empty. Unless I set the subform to AllowAdditions = No, now I get the #Error when there are no records (I set subform to DataEntry=Yes so there are no records when form opens). This removes New Record row and results in calc error. I do no know why subform textbox still shows empty but main form shows #Error.

 

[Gasman]

There is no Null() function. There are Nz() and IsNull() and Is Null.
Try this in PartsTotal:
=Nz(Sum([fieldname]), 0)

Sorry, what was I thinking?

 

[June7]

I edited my previous answer and no longer has that suggestion because it did not work in my test. I should have tested before posting.

 

[Pat Hartman]

I can't research this now but you could try IIf(somefield & "" = "", 0, somefield)