Solved

Add Records "on-the-fly"?

Posted on 2004-08-05
8
416 Views
Last Modified: 2012-05-05
I am creating a form for an Invoice database table. One of the fields is CustomerID. This field is a lookup into a Customer database table. It successfully does the lookup based on the CustomerID (autoincrement), and displays the Customer name.

Currently, one may enter only customers that exist in the Customer database table.

I now need to provide the ability to enter a Customer that does not exist in the customer table, and for that customer to automatically be inserted into the Customer table.

Note also that currently, only the Customer name is displayed. If I do provide this "add-on-the-fly" ability, I will need to provide the ability for them to enter the other fields for the Customer record. Is there any easy way for these fields to popup only for this "add-on-the-fly" situation?

So what I need to happen is this:
a. If they are entering an existing customer, then they need only indicate the ID (or name, actually, since that is what they see);
b. If they are entering a new customer, then they need to enter all the customer fields, and this record will be added to the customer database.

How is this implemented? Should this be a sub-form? Any assistance would be welcome. Since I am an MS Access newbie, the more detail, the better.

Thank you.
0
Comment
Question by:yisraelharris
  • 3
  • 3
  • 2
8 Comments
 
LVL 26

Expert Comment

by:dannywareham
ID: 11724287
If you create a form and bind it to your customer details table, you can enter a new record direct from there.
Be carefull that you protect your form with some security though as it means giving users the ability to manipulate the table data.

Are there any fields in the table that autopopulate? (autonumber for ID for example?)
0
 

Author Comment

by:yisraelharris
ID: 11724493
Hi. Thank you. I am not sure what you mean.

You write:

1. "Create a form" -- you mean a separate form for the customer fields?

2. "bind it to your customer details table" -- You mean that this form I create updates records in the customer database table?

3. "You may enter a new record direct from there" -- what does this part mean?
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 11724537
OK. Create a form first.
Go to your forms area of the db and click NEW.
A wizard will appear.
In the dropdown box, select the table that you want to enter details into through the form.
Click OK.
A blank form will appear with a list of all of your fields.
Add these to your form.
Run the form - you will see the first record in your table shown in the textboxes on your form.
Cycle through the records by clicking the arrows (record navigators) at the bottom of the screen.

As it stands, if you type into the boxes, you will change the data in the table.
So, go back to design view and right click on the form (square at top left)
Select properties
Select events
Go to on load then code builder
A white window will appear - this is the VBA window
Add the following code:

docmd.GoToRecord ,,acNewRec

Close the window
Now run the form
It will automatically take you to a new record ready to add a new customer
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:yisraelharris
ID: 11724894
I am very grateful for your lengthy response but I have totally lost you.

You outlined the details of how to create a from. This I do know how to do. After all, I already have the Invoice form working.

The more I think about it, the more I realize that I don't really see what your response has to do with my original question at all. Can you please re-read it? I think we may be no longer talking about the same topic anymore.

I am not sure what else to say.
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 11724916
I see what you mean.

I see that you have a form working to view old customers - do you want all users to be able to enter a new customer?
If so, create a button on your existing form that opens the form I've described building.
On the form I described, add a button to save the record and close the form.
Now in the orginal form the user will be able to access the new customer.

0
 
LVL 12

Accepted Solution

by:
Sayedaziz earned 50 total points
ID: 11725150
Check with the following if suits and modify the field name as per urs :

Private Sub customerid_DblClick(Cancel As Integer)
    On Error GoTo Err_CustomerID_DblClick
     Dim lngCustomerID As Long
   
     DoCmd.OpenForm "customer", acNormal, , , acFormEdit, acDialog, "GotoNew"
     Me![customerid].Requery
   
     If lngCustomerID <> 0 Then Me![customerid] = lngCustomerID

Exit_CustomerID_DblClick:
    Exit Sub

Err_CustomerID_DblClick:
    MsgBox Err.Description
    Resume Exit_CustomerID_DblClick

End Sub
~~~~~~~~~~~~~~~

Private Sub customerid_NotInList(NewData As String, Response As Integer)
 Me.customerid.Undo
 MsgBox "Double-click this field to add an entry to the list."
 Response = acDataErrContinue
End Sub
0
 

Author Comment

by:yisraelharris
ID: 11725199
Thank you, Sayedaziz.

I want to make sure I understand this:

You are suggesting that I make a separate customer form, which they can get to if they need to by double-clicking on the customerID?

Sounds promising. I will run it by my supervisor.

There were a couple of lines I didn't understand. What do they mean?

1. I didn't see where lngCustomerID gets assigned a value.
2. Me.customerid.Undo
3. Response = acDataErrContinue
0
 
LVL 12

Expert Comment

by:Sayedaziz
ID: 11728844
Here is the details :

1. For your purpose u don't need lngcustomerid , since i have extracted codes from  
    my programme and forget to remove this.

2. Me.customerid.Undo will clear the data entered by users which is not available in the list.

3. Response = acDataErrContinue will supress access default error message of Not in List.

0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How Can I Grab This Information off a PDF Form 23 49
Documents and settings folder 30 78
Should I keep recordsets open? 3 24
2 IIF's in Access query 25 32
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question