FORM to SEARCH, DISPLAY and PRINT 1 Record at a time

[GoodTimeMandi]

I have a form in access that I would like to use, to search and display information on 1 record at a time. I want the user to be able to type in their own search criteria, based on 3 options, and display all of the data in the table pertaining to a vehicle they are searching.

I then would like them to be able to print the form as displayed with the 1 record.

Lastly I would like my users to be able to filter and see all of the vehilces they have by model, then by location and run a report.

The form is based on the table I imported, but I would like to start with all cell blank and have them populate by the users input.

Can someone please help me!?

 

[plog]

Can someone please help me!?

Yes, but not generically. When you make posts like that it sounds like you are looking for a free programmer. Your not likely to get that here.

What you will get is people who are happy to help you solve specific issues you've encountered while trying to accomplish something on your own. You didn't post a specific issue and haven't detailed what steps you have taken to accomplish what you want.

Do you have any specific questions?

 

[GoodTimeMandi]

I apologize, I was meaning to introduce what I am trying to achieve and see if it's doable without doing a bunch of code.

I am having issues getting my textboxes to allow user input (that's my first road block.

Basically, I need advice, is this something that can be easily achieved or should I scrap it and try another program other than access?

 

[plog]

Its not going to take a bunch of code, but some code. Its going to be some work, but you have identified multiple parts to it, so my advice is divide and conquer. Work on one part (creating a search form) and work towards it. When done, move to the next part, etc. etc.

As for your textboxes not allowing user input, you either used the wrong control or you have set a property of it incorrectly--you might have made it Enabled = False or Locked=Yes.

 

[GoodTimeMandi]

Do you know is it easy to translate the verbage in my code from an excel userform, to the access form?

Right now, I have a user interface in excel, but I wanted to convert it over to access, so users can pull reports and such.

Just asking for advice, opinions... thank you in advance.

This is my code;

Private Sub CommandButton5_Click()
[SERIAL].Value = ""
[REG].Value = ""
[VIN].Value = ""
[LIN].Value = ""
[REG].Value = ""
[NSN].Value = ""
[MODEL].Value = ""
[MFGDATE].Value = ""
[WARRANTY].Value = ""
[SHIPDATE].Value = ""
[FLDDATE].Value = ""
[DODAAC].Value = ""
[UIC].Value = ""
[COMPO].Value = ""
[CITY].Value = ""
[HISTORY].Value = ""
[STATE].Value = ""
End Sub
Private Sub REG_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then
Call REGSEARCH_Click
End If
End Sub
Private Sub REGSEARCH_Click()
Do
DoEvents
ROW_NUMBER = ROW_NUMBER + 1
ITEM_IN_REVIEW = Sheets("Oshkosh Inventory").Range("L" & ROW_NUMBER)
If ITEM_IN_REVIEW = REG.Text And Not IsEmpty(ITEM_IN_REVIEW) Then
SERIAL.Text = Sheets("Oshkosh Inventory").Range("A" & ROW_NUMBER)
VIN.Text = Sheets("Oshkosh Inventory").Range("M" & ROW_NUMBER)
LIN.Text = Sheets("Oshkosh Inventory").Range("C" & ROW_NUMBER)
NSN.Text = Sheets("Oshkosh Inventory").Range("D" & ROW_NUMBER)
MODEL.Text = Sheets("Oshkosh Inventory").Range("B" & ROW_NUMBER)
MFGDATE.Text = Sheets("Oshkosh Inventory").Range("P" & ROW_NUMBER)
WARRANTY.Text = Sheets("Oshkosh Inventory").Range("O" & ROW_NUMBER)
DODAAC.Text = Sheets("Oshkosh Inventory").Range("J" & ROW_NUMBER)
UIC.Text = Sheets("Oshkosh Inventory").Range("K" & ROW_NUMBER)
COMPO.Text = Sheets("Oshkosh Inventory").Range("G" & ROW_NUMBER)
CITY.Text = Sheets("Oshkosh Inventory").Range("E" & ROW_NUMBER)
STATE.Text = Sheets("Oshkosh Inventory").Range("F" & ROW_NUMBER)
SHIPDATE.Text = Sheets("Oshkosh Inventory").Range("H" & ROW_NUMBER)
HISTORY.Text = Sheets("OSHKOSH INVENTORY").Range("Q" & ROW_NUMBER)
FLDDATE.Text = Sheets("Oshkosh Inventory").Range("I" & ROW_NUMBER)
GoTo Endloop
End If
Loop Until ITEM_IN_REVIEW = ""
MsgBox "NO RECORD FOUND", vbInformation, "WARRANTY LOOKUP"
Endloop:
End Sub
Private Sub SERIAL_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then
Call SERIALSEARCH_CLICK
End If
End Sub
Private Sub SERIALSEARCH_CLICK()
Do
DoEvents
ROW_NUMBER = ROW_NUMBER + 1
ITEM_IN_REVIEW = Sheets("Oshkosh Inventory").Range("a" & ROW_NUMBER)
If ITEM_IN_REVIEW = SERIAL.Text And Not IsEmpty(ITEM_IN_REVIEW) Then
REG.Text = Sheets("Oshkosh Inventory").Range("L" & ROW_NUMBER)
VIN.Text = Sheets("Oshkosh Inventory").Range("M" & ROW_NUMBER)
LIN.Text = Sheets("Oshkosh Inventory").Range("C" & ROW_NUMBER)
NSN.Text = Sheets("Oshkosh Inventory").Range("D" & ROW_NUMBER)
MODEL.Text = Sheets("Oshkosh Inventory").Range("B" & ROW_NUMBER)
MFGDATE.Text = Sheets("Oshkosh Inventory").Range("P" & ROW_NUMBER)
WARRANTY.Text = Sheets("Oshkosh Inventory").Range("O" & ROW_NUMBER)
DODAAC.Text = Sheets("Oshkosh Inventory").Range("J" & ROW_NUMBER)
UIC.Text = Sheets("Oshkosh Inventory").Range("K" & ROW_NUMBER)
COMPO.Text = Sheets("Oshkosh Inventory").Range("G" & ROW_NUMBER)
CITY.Text = Sheets("Oshkosh Inventory").Range("E" & ROW_NUMBER)
STATE.Text = Sheets("Oshkosh Inventory").Range("F" & ROW_NUMBER)
SHIPDATE.Text = Sheets("Oshkosh Inventory").Range("H" & ROW_NUMBER)
HISTORY.Text = Sheets("OSHKOSH INVENTORY").Range("Q" & ROW_NUMBER)
FLDDATE.Text = Sheets("Oshkosh Inventory").Range("I" & ROW_NUMBER)
GoTo Endloop

End If
Loop Until ITEM_IN_REVIEW = ""
MsgBox "NO RECORD FOUND", vbInformation, "WARRANTY LOOKUP"
Endloop:
End Sub
Private Sub UserForm_Activate()
TESTwarranty.Show vbModeless
End Sub
Private Sub VINSEARCH_Click()
Do
DoEvents
ROW_NUMBER = ROW_NUMBER + 1
ITEM_IN_REVIEW = Sheets("Oshkosh Inventory").Range("M" & ROW_NUMBER)
If ITEM_IN_REVIEW = VIN.Text And Not IsEmpty(ITEM_IN_REVIEW) Then
REG.Text = Sheets("Oshkosh Inventory").Range("L" & ROW_NUMBER)
SERIAL.Text = Sheets("Oshkosh Inventory").Range("A" & ROW_NUMBER)
LIN.Text = Sheets("Oshkosh Inventory").Range("C" & ROW_NUMBER)
NSN.Text = Sheets("Oshkosh Inventory").Range("D" & ROW_NUMBER)
MODEL.Text = Sheets("Oshkosh Inventory").Range("B" & ROW_NUMBER)
MFGDATE.Text = Sheets("Oshkosh Inventory").Range("P" & ROW_NUMBER)
WARRANTY.Text = Sheets("Oshkosh Inventory").Range("O" & ROW_NUMBER)
DODAAC.Text = Sheets("Oshkosh Inventory").Range("J" & ROW_NUMBER)
UIC.Text = Sheets("Oshkosh Inventory").Range("K" & ROW_NUMBER)
COMPO.Text = Sheets("Oshkosh Inventory").Range("G" & ROW_NUMBER)
CITY.Text = Sheets("Oshkosh Inventory").Range("E" & ROW_NUMBER)
STATE.Text = Sheets("Oshkosh Inventory").Range("F" & ROW_NUMBER)
SHIPDATE.Text = Sheets("Oshkosh Inventory").Range("H" & ROW_NUMBER)
HISTORY.Text = Sheets("OSHKOSH INVENTORY").Range("Q" & ROW_NUMBER)
FLDDATE.Text = Sheets("Oshkosh Inventory").Range("I" & ROW_NUMBER)
GoTo Endloop
End If
Loop Until ITEM_IN_REVIEW = ""
MsgBox "NO RECORD FOUND", vbInformation, "WARRANTY LOOKUP"
Endloop:
End Sub
Private Sub VIN_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then
Call VINSEARCH_Click
End If
End Sub

 

[plog]

I'm pretty sure you are going to have to start from scratch. That code doesn't really translate into a database system.

 

[Minty]

I suspect what you need is really quite straight forward to achieve in Access, but we can't "see" where you are trying to get to.
However you could post a picture of your current excel form and label it appropriately I'm sure it's do-able.

 

[GoodTimeMandi]

Here is the userform that I have re-created in access as well. I have my print, clear and exit buttons good to go, but I am having a hard time translating the other textboxes.

 

[Minty]

Okay - You don't need 3 boxes and three search buttons.
One box, one button and an option group to select which item you want to search for will probably make things easier, as you can use the same search code but just look for the single text.

Can I borrow an HTV ?

 

[GoodTimeMandi]

I am not sure how I like the options box. But, do you know how easy is it to have all my other text boxes auto populate based on my searched text?

I have been trying to get an HTV for years now, they just won't give one up!!

 

[Minty]

Are your searched fields unique? Or would you have multiple search results?

If they are unique records it's pretty straight forward.

 

[GoodTimeMandi]

uGHHH, I am having a difficult time with this search. Here's my new user menu, I just want to be able to type in one of the 3 search criteria (serial#, reg# or vin#). I tried the option group, but couldn't get it to work.

If I enable to navigation bar on the form, I can get all my data to display, but I want my textbox to perform this action, not the navigation bar. Can someone pretty please help me?

I have my clear button and print buttons both working and set up.

 

[Minty]

Have a look at the db posted here http://www.access-programmers.co.uk/forums/showpost.php?p=1463064&postcount=6
There is a very basic option group search that you could apply.

If you get stuck post back the code you are using and I'm sure we can assist.

 

[plog]

Does this have to load the data into a form? I can save you a few headaches if you are willing to load the data into a Report object instead. You will still have the form to input your criteria, its just that when you click the button a report will pop up and display the data (in the format you want, similar to your form).

If not, its back to what I said--divide and conquer. Instead of trying to make the form work for all 3 criteria, or any criteria, just get it to load 1 record when you click the button. Once you have that working you can then get the criteria input working for 1 criterion, then another, then al 3.

Again, though, if you load the data into a report it will be easier.

 

[GoodTimeMandi]

I really want the bound textboxes to populate based on the "keyword/number" search, in one form.

I got my reports working, clear and print buttons working (with the exception of the error when the text box is blank).

I am still struggling with the custom report, using 3 combo boxes to filter a subreport (by clicking from the main screen, it opens another form to create your own report).

I have attached my db, if anyone wants to check it out or fiddle with it!

Thank you for all of the advice, I think I need to go back to school on this. Just can't seem to get my controls right.

 

[AccessBlaster]

Once you start getting the results you need, then you can >> filter >>print

 

[GoodTimeMandi]

I sooooo appreciate it, but I still cannot get it to work on my end. I don't know what I am doing (or doing wrong)

 

[GoodTimeMandi]

I can get my query to work, but it opens the query in a new window. I want the query results to populate in my main window. Can this be done?

 

[plog]

Can this be done?

Yes, but its not an elementary task. Since you've chosen to do this solely in a form, you are going to have to do this with VBA.

You will need to populate a RecordSet (https://msdn.microsoft.com/en-us/library/office/ff197799.aspx?f=255&MSPPError=-2147217396). To get to the specific data record you want, you can compose an SQL query and use that SQL as the basis of the recordset, or you can open a table then use the filter property of a recordset (https://msdn.microsoft.com/en-us/library/office/ff837300.aspx). Once you have the recordset on the record you need, you then populate all your form inputs with the respective field from your recordset.

That will get your form populated. Again, like I said, if you weren't married to a form, you could do this even simpler as a Report.