Forms

[hardhitter06]

Hi all,

I'm having trouble on how I would go about doing this:

I have a table that displays Acct#, Vendor, Contact and Address. What I would like to do is be able to display all records in somewhat of a datasheet form (although, I've used continious forms and kind of like how that looks) [open to suggestions tho]. So I set up the form to search by Acct #, if i dont enter an Acct #, all records will be displayed on a continious form.

So here's what I really want to do. I want to be able to enter a new record from that screen, have something pop up for me to enter in the 4 fields and then be able to save it and have it show up on the list of forms. Is this possible? Would i use a subform?

I appreciate any suggestions.

Josh

 

[Scottyk537]

Here's one way of doing it......
Create a new form with the table you are referencing to as the recordsource and add the 4 controls to the form. Set the forms properties:
Modal - yes
Pop-up - yes
On the OnLoad event of this form, write code:

DoCmd.GoToRecord , , acNewRec

Put two buttons on this form: "Close" and "Save"
On your "close" button, write this code:

DoCmd.SetWarnings False
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True
DoCmd.Close

On your "save" button, write this code:

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.Close
forms!MainForm.requery

Where "mainform" is the name of your continuous form.
Add a button on your continous form "New Record".
Use the wizard to make this button open the form you just created with no linked between the two.

I will aslo normaly write code on the "save" button to ensure that all 4 fields are filled out.........
Hope this helps
Scott

 

[hardhitter06]

Scott,

First off thanks for your help,

I'm getting an error when i run the subform.

It says "run-time error '2105':
You can't go to a specified record."

When i hit debug, it brings me to the on event line of code:

Private Sub Form_Load()
DoCmd.GoToRecord , , acNewRec
End Sub

Is this because I have a search set up on my continuous form? I hope not because Id like to give the user the option to search for the acct and if its not in the system, be able to click a button to toggle up an input form. Let me know if you can, thanks again!

 

[Scottyk537]

What is the recordsorce for this new form? A table, or a query?
If it is the table that your continous form is using, and you are still having problems, you might have to create a new query with 4 fields and use this as the recordsouce for your pop-up form.
It should not be a subform, rather a new form that is not linked to your continuous form in any way.

And your search feature should not be affected by any of this...................

 

[hardhitter06]

?

Scott,

I'm still having trouble with this. I am using a table as my record source. I created a query (that uses this table), trying your suggestion, but there still seems to be a bug. Any more help?

 

[hardhitter06]

Also,

that new query i tried making using the same table as my continious form does (well, references a query that is the same table), does that matter?

 

[Scottyk537]

Yes, this might be the problem if your recordset is locked.
Try this, with your continuous form open, run the query that is the recordsource for your new form. Are there records shown? Can you add a new record in the query?

 

[hardhitter06]

I have a account number search on that query. If i leave it on, and select enter, all records are shown. If I take off the search, again all records are shown. But both don't allow me to add another record...

 

[Scottyk537]

There's your problem, you are trying to go to a new record in a query that will not allow you to create a new record.
Back to what I said earilier in this thread, Create a new query that is looking at the table in reference that is not linked to your mainform.
This new form will only be used to add a new record, right? If so, there is no reason to use the recordsource of your continuous form with the search feature.

 

[hardhitter06]

I'm not sure if I'm following,

This is what I've done:

I have the continious form named "frmDeptSearch" linked to the query "qryDept" using the table tblDept.

On my pop-up form named "Test", im using the new query called "qryTest" using the table tblDept.


On the continious form I have a search using the acct number to limit the records if the user knows the account number. If he doesn't, he selects enter and all records are shown. My pop up form linked with qryTest doesn't have any search options.

This is how i have it set up, what do i need to change?

 

[Scottyk537]

It sounds like you have it setup OK.
Run the "qryTest". Are you able to add a new record to this query?

 

[hardhitter06]

When I run it, it shows all of the records already stored but doesnt allow me to add. I realize this is my problem, how do i make it so i can add? Do i need a special type of query?

 

[Scottyk537]

On the properties of the query:
"Recordset Type" should be set to "Dynaset"
not "snapshot"

 

[hardhitter06]

Ok...this is what is going on

When I have the Main form open, it doesn't allow me to add to the query, but when it's closed, I'm able to.

Also, my forms are set to dynaset...is there a place where that same setting is for queries?

 

[hardhitter06]

if you want, i can post a copy of my database...

 

[Scottyk537]

Yes, please post....
In the design view of the query right click in a blank area, and go to properties.

 

[Scottyk537]

Yes, please post......

 

[hardhitter06]

I'm actually pretty close now. From my main form, i can click new record and the new form pops up. I enter data and hit close, which doesn't save the record and closes out. When i hit save, its telling me it can't find the form frmDept...and the funny thing is I haven't created a form named that...

 

[Scottyk537]

Check your code behide the "save" button

 

[hardhitter06]

here ya go..