Save and create new record (1 Viewer)

Machino · Mar 26, 2019

Hi All
I am a newbee (somewhat). I recently created a new database for a client. The problem I am encountering is that I would like to start a new record once I have selected the save button. Once the user starts to fill out the form a PO# is automatically generated. After the user has has filled out the form they select the save button to record the entry. Currently, I am able to save but not blank out the fields.

Next, I have 3 other tabs that are used to store Clients, Vendors and Staff names which are then used to populated combo dropdown lists on the PO Entry form. If some could help me setup one of these correctly I can most likely figure out how to do the rest. I am uploading the database I am working on. Any help is greatly appreciated. Thank you
Machino

Gasman · Mar 26, 2019

You could use the acNewRec option for this command?

https://docs.microsoft.com/en-us/office/vba/api/access.docmd.gotorecord#example

mike60smart · Mar 26, 2019

Hi Machina

All you need on your PO Form is an Add New PO Button

All the Other tabs just needed the Forms added correctly to each specific Tab.

I would recommend that you do NOT use the PO_ID as your Purchase Order Number.

The PO_ID is an Autonumber and should only be used to uniquely identify each record in the PO Table.

You should have a field specifically to record each Purchase Order Number. This can be added automatically in Access by using the following example:-

In the Before Insert of the Form

Me.InvoiceNr = Nz(DMax("InvoiceNr","tblCustomerOrderdates"),0) + 1

View attachment Strathmoor Electric PO.zip

Machino · Mar 26, 2019

Hi mike60smart

Thank you very much for you suggestions and help. I was able to incorporate a great of comments and make them work. I added a new field in my PO_HISTORY table to address the PO number (PO_NO) while leaving the PO_ID as the primary key. At the moment I have two issues that I cannot make work. I set the PO_NO with a start value of 4000 and I placed the code you suggested (with the appropriate changes) in the Before Insert event but it did not work. Can you take a look this and let me know where I went wrong. Two, on the CLIENT, VENDOR and ORDERED BY forms I would like to hide the rule bar on the left. I am attaching the updated database. Again, thank you very much for you assistance with this.
Machino

mike60smart · Mar 26, 2019

Hi Machino

The db has been updated.

To hide the Rule Bar on the Left of the Form you need to set the properties of the Forms to those shown in the attached:-

The generation of the PONr is achieved by using the Before Update and you did not need a default value of 4000 in the table field

View attachment Strathmoor Electric PO-3-26-2019.zip

Machino · Mar 27, 2019

Hi mike60smart

Thanks for the updated database and the jpeg with the settings for turning off the side bar. I tried incorporating the changes you made to my more recent file and for some reason I am getting a method error on the PONr. All of the fields have been renamed and configured to match what you had sent me. I will not work on this database until I get your comments back. Aside from getting the PO feature to increment correctly, I am having trouble with two I believe minor issues. I added a print preview feature based on a query. It was working fine but now it isn't. The other is that I generated a report and two of the field are showing up as numbers instead of names. I know why this is happening but I tried adding a Dlookup to the report field (as you will see) with no luck at all. Thanks a mil for your help with this.
Machino

mike60smart · Mar 27, 2019

Hi

A few things wrong with your tables.

See the attached.

If you need to discuss this then PM me.

View attachment Strathmoor Electric PO-3-27-2019.zip

Machino · Mar 27, 2019

Re: Save and create new record
Hi mike60smart
I see what you have done and it has resolved most of the issues. The only thing that is not working properly is that you select the Print Report button a syntax error message appears (see attached jpeg). Also, do you know why the date function in the report is not displaying correctly. Thanks a mil. I really do appreciate your help with this.

Machino

mike60smart · Mar 27, 2019

Hi Machino

I placed an On Click Event on the Command Button to Preview with Report based on the current PO_ID on the Form

Check that this code is on your version

Machino · Mar 27, 2019

This is the code I found in the On click event. Thanks.

Private Sub Command125_Click()
On Error GoTo Command125_Click_Err

If Me.Dirty Then Me.Dirty = False

DoCmd.OpenReport "PO_HISTORY", acPreview, , "[PO_ID]=" & Me.PO_ID

Command125_Click_Exit:
Exit Sub

Command125_Click_Err:
MsgBox Error$
Resume Command125_Click_Exit

End Sub

mike60smart · Mar 27, 2019

You have to make sure that PO_ID is actually included in your Report

mike60smart · Mar 27, 2019

Hi Machino

It would probably help if you uploaded your actual Db

Machino · Mar 28, 2019

Hi mike60smart
Here's the latest DB. The [PO_ID] is in the report. I'm not sure what's going on. Thanks.

Machino

mike60smart · Mar 28, 2019

Hi Machino

You had not added the PO_ID to the Form

The code :-

DoCmd.OpenReport "PO_HISTORY", acPreview, , "[PO_ID]=" & Me.PO_ID

This is checking the Current PO_ID on the Form and if it is not there then you get the error.

View attachment Strathmoor Electric PO-3-27-2019.zip

Machino · Mar 28, 2019

Hi mike60smart
I removed the PO_ID's you just added because they didn't give the desired results. The Print Report button is supposed to show a printable list of PO's that are still outstanding or not completed. I added a set focus on the find record event procedure which I think is working correctly. Please take a look at it. The last oddity that I am encountering is that if you run the Print Report button and close the preview properly. When you select the Find Record button it displays the Print Preview again. It seems to be triggering the print preview again and again before showing the filled form. Curious. I am attaching the latest version of the DB. Thanks.
Machino

mike60smart · Mar 28, 2019

Hi

I am a little puzzled as to how you can say show a list of PO's that are have a Status of outstanding or not completed.

The Only status you currently have is a Yes/No ??

mike60smart · Mar 28, 2019

Hi

I replaced your Print Preview Command Button with my version.

View attachment Strathmoor Electric PO-3-28-2019.zip

Machino · Mar 29, 2019

Hi mike60smart

I figured out the problem with the print preview. Everything is working fine for now. Thank you very very much for all of your assistance. Take care.
Best
Machino

Machino · Apr 15, 2019

Hi Mike60smart
In the process of demonstrating how the database works I discovered a couple of anomalies. When you open any of the other tabs the fields do not initial as blank. Also, I found that all of the new entries made to the underlying tables through the corresponding forms (ie, Clients, Vendors, etc.) were not showing up in the main PO entry form. I added several vendors and staff in the demonstration but when I when to the dropdown lists on the main form they did not show. However, they were added to the tables. Any suggestions on how to fix these two issues? Thank you.
Machino

I am attaching the latest database.

mike60smart · Apr 15, 2019

Hi

I modified your relationships so that your Clients table is now named tblCustomers

I linked tblCustomers and PO_History by CustomerID

You do not need to store JobNr in the PO_History table is this is an Entity of the Customer

View attachment Strathmoor Electric PO-4-15-2019.zip

Save and create new record (1 Viewer)

Registered User.

Attachments

Enthusiastic Amateur

Registered User.

Registered User.

Attachments

Registered User.

Registered User.

Attachments

Registered User.

Registered User.

Attachments

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Attachments

Registered User.

Registered User.

Attachments

Registered User.

Registered User.

Registered User.

Registered User.

Attachments

Registered User.

Similar threads

Users who are viewing this thread