Solved

Help with a form recordsource problem

Posted on 2006-06-21
8
445 Views
Last Modified: 2008-02-01
Hey experts,

I have a 2 part new data entry form. On the first form, unbound, the user enters in the PONumber, Vendor, etc. and clicks continue. This runs an insert query to add the data to the main table. I also added some code to store the PONumber in a module to be used as the record source criteria of the second form. The final bit of code for my continue button is this:

    mdlDatePicker.SetID = txtPurchaseID.Value

    stDocName = "frmNewPOStep2"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.Close acForm, "frmNewPOStep1"

Then the code in my module is this:
Public Property Get ID() As Integer
    ID = intID
End Property
Public Property Let SetID(x As Integer)
    intID = x
End Property

And then my query that's used for my recordsource on the second form is this:

SELECT tblPurchases.*
FROM tblPurchases
WHERE tblPurchases.PurchaseID=id();

If I run the query by itself, it gives me the correct record information, but when I go through the new order process and go to the second form, no information is being pulled and my bound textboxes aren't showing any data. I'm stuck as how to fix this. Thanks for any help.

Bryan
0
Comment
Question by:bng0005
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 26

Expert Comment

by:dannywareham
ID: 16953024
If the query is giving teh correct info, then teh recordsource is fine.
It's probably your form that needs refreshing to show the changes:


'(On the form On Open event)
Me.Form.Requery
0
 
LVL 1

Author Comment

by:bng0005
ID: 16953047
no luck with that.

Tried in on open and on load, with both requery and refresh
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 16953068
Another method is to pass the recordsource across at form open:

Dim mySQL as String
mySQL = "SELECT * FROM tblPurchases WHERE PurchaseID=" & ID & ";"
DoCmd.OpenForm "frmNewPOStep2",,,,,OpenArgs = mySQL


then, on the form open

Me.Form.Recordsource = OpenArgs
Me.Form.Requery


Idea..?
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 1

Author Comment

by:bng0005
ID: 16953143
Getting an invalid use of Null, saying that OpenArgs is null

Here's my updated code

    strSQL = "SELECT * FROM tblPurchases WHERE PurchaseID=" & txtPurchaseID.Value & ";"
    stDocName = "frmNewPOStep2"
    DoCmd.OpenForm stDocName, , , stLinkCriteria, , , OpenArgs = strSQL
    DoCmd.Close acForm, "frmNewPOStep1"

I get the null on the  Me.Form.Recordsource = OpenArgs in the frmNewPOStep2 on open event
0
 
LVL 26

Accepted Solution

by:
dannywareham earned 500 total points
ID: 16953170
OK.
In a Module, add this:

Public myFormSQL as String

Then, change your code to this:

myFormSQL = "SELECT * FROM tblPurchases WHERE PurchaseID=" & txtPurchaseID.Value & ";"
    stDocName = "frmNewPOStep2"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.Close acForm, "frmNewPOStep1"


On the form open of frmNewPOStep2:

Me.Form.Recordsource = myFormSQL


This is using a global variable to hold teh value
0
 
LVL 1

Author Comment

by:bng0005
ID: 16953228
No errors, but no data showing up either. Before trying something else, how would I set the controlsource for the textboxes this way, through code as well, ie
   txtPurchaseID.ControlSource = ?
0
 
LVL 1

Author Comment

by:bng0005
ID: 16953553
ok well I deleted the control source of the textbox and tried adding in through vba once the recordsource was set. Here's my form on load event now

    Me.Form.RecordSource = myFormSQL
    Me.txtPurchaseID.ControlSource = Me.Recordset("PurchaseID").Name
    Me.Form.Requery

but still nothing showing up.

I'll explain what I'm trying to do as you might have an easier way than I thought up that would work. The 2 forms are used to enter a new Purchase Order into the database.  I have 2 main tables, tblPurchases and tblPurchaseItems, with 1-M relationship. My first form just saves the bare minimum of the information so I can have a PurchaseID in the database. The second form would use the PurchaseID as the master field to link with my subform so I can allow the user to enter in multiple items on the subform in datasheet view, and then update the main table with any additional information entered in by the user. Hope that all made sense.
0
 
LVL 1

Author Comment

by:bng0005
ID: 16954723
Dunno how I got it working, but I tried another compact and repair then redid the form from scratch and it's working like it should. Thanks
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

690 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