Populate controls on form via query

simonuk

New member
Local time
Today, 12:31
Joined
Jan 13, 2011
Messages
5
Hi everybody

I'm a total access/vba novice and looking for some advice on how to populate a number of text box controls on an access form with data from a table.

There will be around 15 fields that I need to be auto populated once the user has entered a member number.

I can get this to work using dlookup but it is a bit slow and through my research its seems that dlookup is not preferred?

I have tried to use the following code:

Private Sub Command7_Click()

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Select * FROM dbo_MemberMain WHERE [MemberNo] = Me.[memberNo]")
Me.[Forename] = rs!Forename

rs.Close

Set rs = Nothing
Set db = Nothing


End Sub

I tested the query through the query designer and it works but when I come to click the button on the form nothing happens. The code complies but doesn’t seem to do anything.

Like I said I am a total vba novice so the above may be total rubbish

Any advice would be most welcome.
 
If all you need to enter is a member number, then do this. Create a Select query based on the fields you have on the form. In the query include the member number field. In the query grid in the criteria field for the member number put the following:

[Please enter your member number]

Include the brackets. Open the form in design view and make that query the Record Source and save it. When the form is opened, it will prompt the user for the member number. It will say "Please enter your member number." This will populate the form with that record.
 
If all you need to enter is a member number, then do this. Create a Select query based on the fields you have on the form. In the query include the member number field. In the query grid in the criteria field for the member number put the following:

[Please enter your member number]

Include the brackets. Open the form in design view and make that query the Record Source and save it. When the form is opened, it will prompt the user for the member number. It will say "Please enter your member number." This will populate the form with that record.

Hi thanks for your reply.

The recordsource of the form is already set to a table called member_requests.

The purpose of the form is to create a new record in this table to store members requests.

I want to lookup some of the fields from dbo_membermain this being a seperate SQL database. Some of the fields are just to be looked up for information purposes and some will be recorded in the requests table
 
If you are adding records, then use drop down boxes for the fields you need to look up information in.

1) Change these fields to combo boxes.
2) Go into design view.
3) On the Data Tab or All Tab find Row Source Type.
4) Select "Table/Query."
5) Click in the Row Source line. You will get 3 litlle dots on the end. Click on them. This will open up a query type view.
6) You have a couple of options here.
a) Find the table with the field yuo want to use as a lookup. Or,
b) Create queries before step 1 and select those queries for the
appropriate Row Source
7) Close the Combo Box.
8) Save the form.

I hope this is what you need.
 
If you are adding records, then use drop down boxes for the fields you need to look up information in.

1) Change these fields to combo boxes.
2) Go into design view.
3) On the Data Tab or All Tab find Row Source Type.
4) Select "Table/Query."
5) Click in the Row Source line. You will get 3 litlle dots on the end. Click on them. This will open up a query type view.
6) You have a couple of options here.
a) Find the table with the field yuo want to use as a lookup. Or,
b) Create queries before step 1 and select those queries for the
appropriate Row Source
7) Close the Combo Box.
8) Save the form.

I hope this is what you need.

There are over 10,000 records in the central dbo_membermain table so having users pick from a drop down box would be a pain.

I ideally want the user to enter a member number click a button and then have a number of different fields on the form populate from the main database. I can do this with Dlookup but I know that this is frowned upon. I just don’t quite understand how to do it in vba with record select and sql.

 
I am getting a little confused but I think I know what you want to do. I would do this. Create a holding table for the new record. The method I normally use is to create a table that somewhat matches the table that is going to receive the new record. I even name it almost the same, only add the word "Hold" on the end of the name. Example: Main and MainHold

Make it the Record Source for the form.

Create a delete query to delete the data from the hold table.

Create an append query that will prompt for the unique identifier in the record you are trying to pull the data from. Add the other fields that you want to populate the form with. This query will append the data to the "Hold" table.

When the form opens, it will have this data already there. Fill in the rest of the form.

Create another append query and append it to the table you emulated. That is, the table you want to receive the new data. If you don't want the same data, don't include it in this append query.

So, the process would be:

Close your switchboard, or whatever you call your form.
Run the append query, prompting for the unique identifier.
Open the form.
Fill the form in.
Append the hold table to your main table.
 
I am getting a little confused but I think I know what you want to do. I would do this. Create a holding table for the new record. The method I normally use is to create a table that somewhat matches the table that is going to receive the new record. I even name it almost the same, only add the word "Hold" on the end of the name. Example: Main and MainHold

Make it the Record Source for the form.

Create a delete query to delete the data from the hold table.

Create an append query that will prompt for the unique identifier in the record you are trying to pull the data from. Add the other fields that you want to populate the form with. This query will append the data to the "Hold" table.

When the form opens, it will have this data already there. Fill in the rest of the form.

Create another append query and append it to the table you emulated. That is, the table you want to receive the new data. If you don't want the same data, don't include it in this append query.

So, the process would be:

Close your switchboard, or whatever you call your form.
Run the append query, prompting for the unique identifier.
Open the form.
Fill the form in.
Append the hold table to your main table.

I would really like to do it through VB. I can almost get my code to work; the problem seems to be with the sql statement. In the code below it works and looks up member 3324. The problem is when I try and get the 'where' part of the statement to use the value in the form, it wont work


I have tried:

strSql = "SELECT * FROM dbo_MemberMain WHERE(dbo_Membermain.memberNo) = [Forms]![Form1]![MemberNo]

but it dosent seem work.



Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql As String


strSql = "SELECT * FROM dbo_MemberMain WHERE(dbo_Membermain.memberNo) =324"


Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset(strSql, dbOpenForwardOnly)

Me.Forename = rs!Forename
Me.Surname = rs!Surname
rs.Close
End Sub
 
This:

strSql = "SELECT * FROM dbo_MemberMain WHERE(dbo_Membermain.memberNo) = [Forms]![Form1]![MemberNo]


Would need to be this:

strSql = "SELECT * FROM dbo_MemberMain WHERE dbo_Membermain.memberNo = " & Forms!Form1.MemberNo

(The parens are also extraneous and not needed - cleans it up a bit. I only use the parens in code when I absolutely need to separate certain conditions like
(Something And SomethingElse) OR (Anotherthing And OneMoreThing)
 
Thanks that works great.

I also need to lookup fields from a different table where I need to specific two different criteria.

If tried using:

strsql = "SELECT * FROM dbo_MemberAddress WHERE dbo_MemberAddress.MemberNo = " & Forms!Form1.memnum And dbo_MemberAddress.AddressType = "HOME"

but its not working.

any ideas?

I need to look up a members record which has the AddressType field as Home.
 
Code:
strsql = "SELECT * FROM dbo_MemberAddress WHERE dbo_MemberAddress.MemberNo = " & Forms!Form1.memnum &" AND dbo_MemberAddress.AddressType = 'HOME'"

Try that
 

Users who are viewing this thread

Back
Top Bottom