VBA Forms search and update

**Raghukumarn** · 01-30-2014, 01:16 PM

Hi team,

I work with the accounts payables team wherein we receive invoices on a daily basis from external service providers and we need to track the incoming invoices and update the status as and when it gets processed.

In order to track them I have built an excel form which helps in updating the invoices into excel as and when we receive it.

However as and when we complete the processing we need to update the status of these invoices. Is there anyway we can have a search utility built in the form which can use the Invoice Number(unique ID) for populating all the details in the form and provide an option for updating the status.

I am attaching the Invoice tracker for your reference.

If anyone can help me with the code for searching and Updating the already inserted invoice data, it will help me in floating this into production. Please let me know if you need any more information.

Thanks a ton for your assistance in advance.

Regards,
Raghu

**Leith Ross** · 01-30-2014, 02:17 PM

Hello Raghukumarn,

Welcome to the Forum!

The attached workbook has the search feature added. There are a few other changes as well. The "OK" button does nothing. It has been replaced by the "Update" button. I created two new macros to make working with the data easier. The macros are ReadRecord and WriteRecord. ReadRecord copies the data from the worksheet to the UserForm. WriteRecord does the reverse. Each of these uses the worksheet row number of the invoice.

Try this out and let me know the results.

**Raghukumarn** · 01-31-2014, 03:09 AM

Thanks a lot for your help Leith.

Integrating my tracker with the codes which you have furnished. I will upload the tracker once it is completed.

Thanks a ton for your help.

Regards,
Raghu

**Raghukumarn** · 01-31-2014, 03:34 AM

Hi Leith

Attached is the updated one.

However the search functionality is not working as per expectation. Can you please take a look.

Regards,
Raghu

**Leith Ross** · 01-31-2014, 12:44 PM

Hello Raghu ,

I see you have multiple entries for the same invoice. The macro was written to find the first invoice number in the database and return that record's information. I will work on a way to let the user choose an invoice with multiple listings.

**Raghukumarn** · 01-31-2014, 11:52 PM

Hi Leith,

I am trying to limit the invoice number to unique Id eo that users cannot enter duplicate records. you can see duplicates now as I was testing. Is there anyway we can assign primary key to each entry.

**Leith Ross** · 02-02-2014, 01:30 AM

Hello Raghu ,

This version of the workbook is almost a complete rework of the first workbook. All the UserForm functions are now controlled by the command buttons on the UserForm.

Users will be alerted when an invoice already exists. The invoice number can only be entered once. The user can simply type in the invoice number in the text box and after pressing "enter" or clicking a different control, the record will be displayed. If the user types in the invoice number and then clicks "Insert", a new record will be added to the next line below the last entry on "Data" provide the invoice number has not be used. Otherwise, the user will be notified the invoice number exists and to edit the record using the "Update" button.

There are two controls missing on the UserForm: Invoice_Receipt_Date, Invoice_Log_Date. Once you add these control, the code for these will need to be added to the UserForm. Try this version and let me know how you like it.

**Raghukumarn** · 02-02-2014, 09:31 AM

Thanks a Ton Leith.

This definitely helps.

Regards,
Raghu

**Raghukumarn** · 03-07-2014, 12:28 AM

Hi Leith,

I have attached the updated tracker with all the relevant details required(thanks to you).

Need the final part of the puzzle fixed. Have inserted a box for UID which needs to be autopopulated for each entry inserted by the users and the same can be used in the search utility as it acts as the primary key.

While Inserting an entry can the UID field be updated with the formula (prev row of UID column + 1) in order to assign an UID sequentially for each entry.

Regards,
Raghu

**Raghukumarn** · 03-07-2014, 02:29 AM

Hi Leith,

Its resolved.

Regards,
Raghu

VBA Forms search and update

LinkBack

Thread Tools

Rate This Thread

Display

VBA Forms search and update

Re: VBA Forms search and update

Re: VBA Forms search and update

Re: VBA Forms search and update

Re: VBA Forms search and update

Re: VBA Forms search and update

Re: VBA Forms search and update

Re: VBA Forms search and update

Re: VBA Forms search and update

Re: VBA Forms search and update

Thread Information

Users Browsing this Thread

Similar Threads

Update listbox (forms)

Access 2007 Forms Update and Add Cross Referenced Field

Search for and Display multiple same date info in separate forms

User Forms for search

Linking user forms together and recording on database with a search function

Bookmarks

Bookmarks

Posting Permissions