VB and finding records

[darbok]

Basically I'm hoping there is some way to use vb or list box or both or a query that my users can hit a button, put in a variable/perimeter and a dataview like list will show the records with the variable/perimeter in them, that they can then select that record and the record pulls up in the form view for them to edit or be able to put a save button with the list so they can change it on the list, hit save. I'm using access as a front end for postgresql that is remote from the users. I've been looking for hours. thanks.

 

[theDBguy]

Hi. Welcome to AWF!

I think what you want is very possible, but it's hard to offer any specific advice without more information.

 

[Uncle Gizmo]

I agree with dB guy, more information is required if you you intend building something yourself in VBA.

I wondered if something like my nifty search form would do the job? Have a look here and if you are interested contact me by PM for more information.

Nifty Search Form - Nifty Access

Nifty Search Form This Advanced but simple to use Search Form is unbelievably easy to set-up. Just create a query and a subform. Following the comprehensive Video Instructions, which demonstrate how to add this incredible utility to your database. Establish yourself as the "Go To" person in your...

www.niftyaccess.com

 

[darbok]

I'm using postgresql as my back end. I'm using access as my front end since that seems to be the only viable program. We put about 5k entries(per sheet) a year into 4 or 5 excel sheets. I'm trying to take this make it a database to make things better, especially with reporting. The thing I am trying to keep in mind is that part of our team of 15 are people who just do basically data entry. They're not stupid but not overly tech savity. The big down fall i'm seeing in access is that there isnt a good way to do a search, have it display as a dataview, then click on the entry and have it appear in the form so they can edit it. I figured out how to make a query search for a specific keyword and bring it up in dataview and edit it there, then have to press alt-F9 or close the query to save it, but they'll be looking for a save button that wont be there. I'm trying to make everything easier not harder with steps that arent needed.

 

[theDBguy]

I'm using postgresql as my back end. I'm using access as my front end since that seems to be the only viable program. We put about 5k entries(per sheet) a year into 4 or 5 excel sheets. I'm trying to take this make it a database to make things better, especially with reporting. The thing I am trying to keep in mind is that part of our team of 15 are people who just do basically data entry. They're not stupid but not overly tech savity. The big down fall i'm seeing in access is that there isnt a good way to do a search, have it display as a dataview, then click on the entry and have it appear in the form so they can edit it. I figured out how to make a query search for a specific keyword and bring it up in dataview and edit it there, then have to press alt-F9 or close the query to save it, but they'll be looking for a save button that wont be there. I'm trying to make everything easier not harder with steps that arent needed.

Hi. Have you tried searching for demoes on search forms? I know there are a few available. Also, have you tried using a form/subform setup so you can have a datasheet display and also have a button?

 

[darbok]

Hi. Have you tried searching for demoes on search forms? I know there are a few available. Also, have you tried using a form/subform setup so you can have a datasheet display and also have a button?

I didnt know that was possible.

 

[theDBguy]

I didnt know that was possible.

Like I said, if we had more information, we can tell you what's possible. Screenshots or mock-ups would be fine.

 

[darbok]

Hi. Have you tried searching for demoes on search forms? I know there are a few available. Also, have you tried using a form/subform setup so you can have a datasheet display and also have a button?

so i figured out how to make a subform that has a dataview and have it display the record on the other form. Trying to make a button appear on the subform doesnt seem possible. I'm sure it is, but making a button doesnt display the button on the subform and the wizard on how to run the button doesnt appear. I'm sure its just me.

can i direct my query to appear in the subform?

 

[theDBguy]

so i figured out how to make a subform that has a dataview and have it display the record on the other form. Trying to make a button appear on the subform doesnt seem possible. I'm sure it is, but making a button doesnt display the button on the subform and the wizard on how to run the button doesnt appear. I'm sure its just me.

can i direct my query to appear in the subform?

I was suggesting to put the button on the main form. If you want a button next to each record, then you'll have to use a continuous form that looks like a datasheet. If you must use a datasheet, you can't use a button but can use a Textbox instead that looks like a button.

 

[darbok]

I was suggesting to put the button on the main form. If you want a button next to each record, then you'll have to use a continuous form that looks like a datasheet. If you must use a datasheet, you can't use a button but can use a Textbox instead that looks like a button.

Ok I figured out how to make it save changes on the sub form. I think and hope my last question is, how do I make the query display in the dataview subform and not make a new dataview tab?

 

[theDBguy]

Ok I figured out how to make it save changes on the sub form. I think and hope my last question is, how do I make the query display in the dataview subform and not make a new dataview tab?

Sorry, I'm not sure I understand that question. Since we can't see your screen, it's hard to say what you're trying to do. Why would you need to create a new tab?

 

[darbok]

Sorry, I'm not sure I understand that question. Since we can't see your screen, it's hard to say what you're trying to do. Why would you need to create a new tab?

My apology. So I have a query button on my main form, that when I press it , it asks "which city" so I put in a city, what I want is the results to appear in the subform, but instead the query opens a new dataview.

Kind of trying to go for something like this.... you put in the search info at the top and it displays the dataform in a subform below. This is just an image I found, i'm not this good.

 

[theDBguy]

My apology. So I have a query button on my main form, that when I press it , it asks "which city" so I put in a city, what I want is the results to appear in the subform, but instead the query opens a new dataview.

Kind of trying to go for something like this.... you put in the search info at the top and it displays the dataform in a subform below. This is just an image I found, i'm not this good.

View attachment 92787

Hi. Thanks for the clarification. As I suggested earlier, if you were able to review any of the demoes on search forms, you would have seen how to do something like this. You didn't show us what your button code looks like, but normally, you wouldn't open a query. Instead, you can refresh/requery a form or subform.

Let me see if I can find a link to a demo for you. You might also try searching, in case you beat me to it.

 

[darbok]

Hi. Thanks for the clarification. As I suggested earlier, if you were able to review any of the demoes on search forms, you would have seen how to do something like this. You didn't show us what your button code looks like, but normally, you wouldn't open a query. Instead, you can refresh/requery a form or subform.

Let me see if I can find a link to a demo for you. You might also try searching, in case you beat me to it.

this is my sql.... I saw something about requeries but i just did register how to do it.

SELECT [public_Tech Completion Tracker].[Cost Center], [public_Tech Completion Tracker].[City], [public_Tech Completion Tracker].[State], [public_Tech Completion Tracker].[REQ#], [public_Tech Completion Tracker].[NCI DVA], [public_Tech Completion Tracker].[Technician], [public_Tech Completion Tracker].[OLD Serial], [public_Tech Completion Tracker].[New Serial], [public_Tech Completion Tracker].[Fedex], [public_Tech Completion Tracker].[POC], [public_Tech Completion Tracker].[Install Date]
FROM [public_Tech Completion Tracker];

Basically its search the public tech completion tracker for the data I want to display in the subform, but instead of putting it in a subform, its making a new dataview page.

 

[Pat Hartman]

You chopped off the top of your form so I can's see the header. I'll describe how to do this the "Access" way for a single lookup field.

In the header of the form add an unbound combo. If you have the wizards turned on, Access will ask you questions. Choose the - find a record on this form - option.

That's about as simple as it gets. However, I don't use this method because my BE databases are generally not Jet/ACE but ODBC instead and so this method is not efficient.

Let us know if this doesn't work for you and we can go on with other options.

 

[darbok]

You chopped off the top of your form so I can's see the header. I'll describe how to do this the "Access" way for a single lookup field.

In the header of the form add an unbound combo. If you have the wizards turned on, Access will ask you questions. Choose the - find a record on this form - option.

That's about as simple as it gets. However, I don't use this method because my BE databases are generally not Jet/ACE but ODBC instead and so this method is not efficient.

Let us know if this doesn't work for you and we can go on with other options.

mine is using obdc to connect to a postgresql.

 

[Pat Hartman]

For a couple of combos and text boxes - use unbound controls in the header. For the form, create a query that uses the unbound controls as criteria.
Where (fld1 = Forms!yourform!cbofld1 OR Forms!yourform!cbofld1 Is Null)
AND (fld2 = Forms!yourform!cbofld2 OR Forms!yourform!cbofld2 Is Null)
AND (dt1 Between Forms!yourform!txtfld3 and Forms!yourform!txtfld4 OR Forms!yourform!txtfld3 is Null)
etc.

Notice the parentheses. They are required in order for this complex expression to be evaluated correctly. It is the "Is Null" part that allows the criteria to be optional. If the criteria is not optional, then omit the OR clauses.

If you have only one criteria field, in its AfterUpdate event, requery the form -- Me.Requery. If you have multiple criteria fields, I would add a button to do the requery.