Software

Ask something
Discussion Locked

auto fill Access form from cust Number

0 helpful
13 replies

by dennisbv

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I have a small application I am doing and I want a customer form to open up with blank fields for data entry(I already can set my form for data entry). But if the customer is already in the database I want it to be after the user enters the customer number all the other fields will automaticly fill in if the customer already exists.

13 replies

by dennisbv

I only want one form. If the customer is there fill in the other information if they are not there add them

by dklippert

A subform will appear as a "Control" or part of the main form.
Look at the Customer Orders or Quartyerly Orders form in the sample Northwind.mdb

by dennisbv

This is how I intend to display an existing customers orders. But If the customer does not yet exist? The part I am working on is to creat an Invoice. So if the customer Exists populate with his info. If they donot Exist then add. Answer three May be what I am looking for

by r. a. caluste

you should insert an event procedure (OnEnter) for that.. here's a psuedo code:

dim rs as adodb.recordset

set rs.activeconnection=currentproject.connection
rs.cursorlocation=adUseClient
rs.find "custno = " & me.txtcustno
if rs.eof = true then
'end of file, no match
'add new record
DoCmd.GoToRecord , , acNewRec
else
me.txtcustname = rs!custname
'populate the fields with the values from the recordset
end if

hope this helps

by dennisbv

I have tryed this and I recieve the Error Object Variable not set on this line
"Set rs.ActiveConnection = CurrentProject.Connection"
The only thing I changed was custno to CustNumber and txtcustno to txtCustNumber. This does look like this may be what I was looking for if I can get it to work.

by r. a. caluste

i forgot several details. make sure you add adodb in your references

dim rs as adodb.recordset.. from vba editor, go to tools/references.. Make sure Microsoft ActiveX Data Object Library is checked.

set rs.activeconnection=currentproject.connection
rs.cursorlocation=adUseClient

'i forgot this :P
rs.open "tablename"

rs.find "custno = " & me.txtcustno
if rs.eof = true then
'end of file, no match
'add new record
DoCmd.GoToRecord , , acNewRec
else
me.txtcustname = rs!custname
'populate the fields with the values from the recordset
end if

by dennisbv

Here are what I have for references
Visual Basic For Applications, Microsoft Acess 9.0 Object Library, OLE Automation, Microsoft ActiveX Data Objects 2.6 Library, Microsoft ADO Ext. 2.6 for DDL and security. I still get the error Object variable or With block not set on the part set rs.activeconnection
Here is my code
Private Sub txtCustName_Enter()
Dim rs As ADODB.Recordset
Set rs.ActiveConnection = CurrentProject.Connection
rs.CursorLocation = adUseClient
rs.Find "CustNumber = " & Me.txtCustNumber
rs.Open (tblCustomers)
If rs.EOF = True Then
'end of file, no match
'add new record
DoCmd.GoToRecord , , acNewRec
Else
Me.txtCustName = rs!CustName
'populate the fields with the values from the recordset
End If
End Sub

by r. a. caluste

dim rs as new adodb.recordset
or
dim rs as adodb.recordset
set rs = new adodb.recordset

to create the recordset object :)

by dennisbv

Now I get runtime error "3704" operation not allowed when object is closed.
my code
Private Sub txtCustName_Enter()

'Dim rs As ADODB.Recordset
Dim rs As New ADODB.Recordset

Set rs.ActiveConnection = CurrentProject.Connection
rs.CursorLocation = adUseClient
rs.Find "CustNumber = " & Me.txtCustNumber
rs.Open (tblCustomers)
If rs.EOF = True Then
'end of file, no match
'add new record
DoCmd.GoToRecord , , acNewRec
Else
Me.txtCustName = rs!CustName
'populate the fields with the values from the recordset
End If
End Sub

If it is a new recordset will it not be empty?

by r. a. caluste

in your code:

rs.Find "CustNumber = " & Me.txtCustNumber
rs.Open (tblCustomers)

you should "open" first before you do other functions such as find. to rewrite it should be:

rs.Open (tblCustomers)
rs.Find "CustNumber = " & Me.txtCustNumber

This discussion is closed to new replies.