Form Based on Query > Adding New Data / New Columns

[kamathmanoj]

I have inherited a 10 year old database that has been through multiple revisions. While I standardize it, I have been given the task of automating one of the forms to accept 2 barcode scans 1 for Employee ID and 1 for Product ID. Pulling the product ID was easy, there was a combo box control that needed to be replaced with a text box that accepts the bar code scan producing the underlying number which is a part of the existing recordset. This pulls up the record for manipulation.

Now the problem:
I need to add "Employee ID:" scanned field which is not part of the recordset currently and write it to the table underlying the query on which this form is based on.

Question:
do I just add the Employee ID as a field in the table, re-run the query to include the Employee ID and then save the form as it is, to append a new record? What other method can I use to make minimal changes and disruption in the currently running database - its a mission critical database.

Thank you for any insight!

Regards,
Rick

[pbaldy]

If you want to save employee with the rest of the transaction then yes, you need to add a field to the table/query/form.

[kamathmanoj]

Thank you so much, Paul. I added the field to the table (it took some finding as it was in another database sitting safely away from my prodding and pushing). I also brought the newly added field into the query. However, when I try to save the record, it does nothing. I have tried DAO.Recordset but it states that there is an error, so I also tried declaring an object. None of those 2 methods works. Adding code I have used before. I am a relative newbie and appreciate any insight!

Regards,
Rick

[kamathmanoj]

With table as Object
Private Sub Save_Command_Btn_Click()
Dim tblRawMaterialRcvd As Object
Dim appAccess As Access.Application
Dim strDB As String


Const strConPath = "H:\Access Database Automation Project\BACKEND"
strDB = strConPath & "PRODSYS_KY.mdb"
Set appAccess = CreateObject("Access.Application")


appAccess.OpenCurrentDatabase strDB


Set tblRawMaterialRcvd = strDB.OpenRecordset("SELECT * FROM [RAW MATERIAL RECEIVED]")
tblRawMaterialRcvd.AddNew
tblRawMaterialRcvd![TAG #] = Me.[TAG #].Value
tblRawMaterialRcvd![COIL NO] = Me.[COIL NO].Value
tblRawMaterialRcvd![ID RAW MATERIAL REC'D] = Me.[ID RAW MATERIAL REC'D].Value
tblRawMaterialRcvd![HET] = Me.[HEAT].Value
tblRawMaterialRcvd![EMPLOYEE_ID] = Me.[EMPLOYEE_ID].Value
tblRawMaterialRcvd.Update
tblRawMaterialRcvd.Close
Set tblRawMaterialRcvd = Nothing
DoCmd.Close
End Sub

With CMD but I am not sure that there is relevant code here...
Private Sub Save_Record_Click()
On Error GoTo Err_Save_Record_Click




DoCmd.RunCommand acCmdSaveRecord


Exit_Save_Record_Click:
Exit Sub


Err_Save_Record_Click:
MsgBox Err.Description
Resume Exit_Save_Record_Click

End Sub

[pbaldy]

I take it the form isn't bound? Do the other fields in the recordset get saved? The path doesn't look like it will resolve correctly.

[kamathmanoj]

Hi Paul,

The form is bound to a select query that is running on the product details table. I was able to atleast manage the records being picked up after automating the scanner bit that was needed. Nothing is getting saved at all. A record is not being created

Regards,
Rick

[pbaldy]

Do the textboxes on the form have fields from that query in the control source? Can you attach the db here?

[kamathmanoj]

Hi Paul,

Thanks for the quick responses.

The text boxes on the form have fields from the query in the Control source, of course. When I scan the Product ID, the fields populate. When I clear and scan another product, the fields populate with that products details. The database is a live one and its built like nothing I have seen, with 2 frontend databases, and 4 backend databases. Tough to copy (apart from any violation I might cause on proprietary data). Is there some code I can try to inject atleast to save 1 part of the record such as a product name, Product ID and Price?

Thank you in advance.

Regards,
Rick

[pbaldy]

You certainly shouldn't post any private data. I just can't see why you're not getting anything saved, and presumably no error message? You're using a different method than I would, but I don't see why it would fail silently.

[kamathmanoj]

Hi Paul,
I have the entire database isolated - managed to move all linkages too from the frontend to point to the local (non-production environment).
Here is a screen shot of the form:


Here is a screen capture of the property sheet:


& this is the query the form is based on:


Please do let me know if you can find something wrong.

Regards,
Rick

[kamathmanoj]

Hi Paul,

In the live database, the analyst showed me that clicking the "STOP" button updates the database. I looked at the event procedure for the Stop button click and it reads as below:

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click


DoCmd.Close


Exit_Command25_Click:
Exit Sub


Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub

I cannot find out how in the world, this could update the database in the backend. Can you please let me know how to proceed?
Thank you - am at my wits end!!

Regards,
Rick

[kamathmanoj]

Hey Paul,

*** Update ***

I cleaned the code a bit by removing all the extra buttons and combo box codes that were created by the previous developers and voila - the database table now updates.

While the question remains on how, everything is working. The Employee ID was not getting updated so I just set the control source to that field in the properties box.

If you remember, I added this field in the form to be able to pick up a scanned bar code. This field is present in the backend table as well as the select query that the form is based on.

I also added the Tag ID text box at the same time, which is pulling the record from the query just fine.

I know it probably sounds crazy but I am feeling happy (that the rest of the record is updating) but lost (because I dont know why the Employee ID is not updating). I feel like a little scream of anguish!!

Where do you think I can find the code so I can ensure that if there are modifications in future, I can find and change the code as required.

Regards,
Rick

[pbaldy]

If a form is bound to a table/query, it will automatically save (or try to) when the form is closed. No code is required, and it's the most common way of connecting a form to a table.

[pbaldy]

Oops, to the question regarding the employee field, that's a bit of a stumper. If the field is in the table and the query, then having the controls source of a textbox be the name of the field (without an "="), it should save just like all the other fields.

[kamathmanoj]

Yessssssss!!!!!!! I changed the Control Source to the name of the ID field and it saves now! Whooppee Part 1 of the issue has been successfully solved. Thank you Paul!! both for your patience and willingness to stick it on, with my issue.

I didnt know that the default behavior of a form based on a select query was to save the data back to the table. If I need to add multiple records, should I then just have an autonumber field added?

Regards,
Rick