Auto Fill

[Phoenyxsgirl]

Ok, I need to autofill a form. I have pretty much figured out how to do this with just one glitch. But the glitch is major.

Its like having a part left over after you repair the washing machine.

When I type in the ID#, the form autopopulates with the records pertaining to that #. It does NOT move to the record pertaining to that # though. It stays on the 1st record. Because saving the new information would involve CHANGING the 1st record, this is bad.

So, how do I get the form to not only autofill but to also move to the appropriate record?

[Toyman]

Post the code you are using for the auto fill

[Phoenyxsgirl]

code .. umm .. well, i used the form wizard in access 2007 and created a form based on a query of two separate tables then made the primary key field a combo box.

I'm not that good to use code yet.

[Toyman]

code .. umm .. well, i used the form wizard in access 2007 and created a form based on a query of two separate tables then made the primary key field a combo box.

I'm not that good to use code yet.

Ok, look at the query in sql view. Copy the sql statement and post it here. What is the purpose of the auto fill. Please provide more information on what you are trying to achieve

[Phoenyxsgirl]

well, the purpose is that I want to be able to pull up records in the form and edit them without having to find record 536 ... I want to pull up client # 19853205

And I had to leave the database computer at work so I won't be able to get the SQL code I'm using til Monday.

[Toyman]

well, the purpose is that I want to be able to pull up records in the form and edit them without having to find record 536 ... I want to pull up client # 19853205

And I had to leave the database computer at work so I won't be able to get the SQL code I'm using til Monday.

I almost think I know the problem. Describe the contents of each of the two tables. Is the relationship between the tables one to many? Is the form bound directly to the query via the recordsource

[Phoenyxsgirl]

The relationship between the tables is only through their main ID #, AppID. The form has the AppID, Name, Contact, Start, End fields. The subform has Job#, JobStart, JobEnd. The form opens on the first record, When I enter the AppID and tab through the fields, it stays on the first record. The subform pulls up jobs according to the AppID but I can't update anything because the record itself is not changing appropriately.

I suspect the answer lies somewhere in Macros - which I am still learning.

I hope this was enough information.

[Toyman]

The relationship between the tables is only through their main ID #, AppID. The form has the AppID, Name, Contact, Start, End fields. The subform has Job#, JobStart, JobEnd. The form opens on the first record, When I enter the AppID and tab through the fields, it stays on the first record. The subform pulls up jobs according to the AppID but I can't update anything because the record itself is not changing appropriately.

I suspect the answer lies somewhere in Macros - which I am still learning.

I hope this was enough information.

In your earlier post, you stated that the main form is based on a query which has two tables linked. Is this correct? If so, describe each table's content and how the tables are related.

From your previous description of how your are trying to navagate through your records with a combo box, I think you are using a one to many relationship link to achieve the auto filling. If this is in fact what you are doing to navagate through the different records, it is incorrect. The one to many relationship link in a query allows you to enter a foreign key in the many side of the relationship and the query pulls the information from the single side. This is used to enter record in the table containing the many side of the relationship and is not ment for navagating through the records. In order to navagate using a search field, you must do a little coding. If this is indeed what you are trying to achieve, replay so, and I will walk you through

[Phoenyxsgirl]

ok good! lets start from scratch. I want to pull up a form and subform. I want the form to have all info from one record and be able to find that record - if it exists already - and be able to create a new record if it doesn't. I also want it to be able to pull up and update related information in the subform.

So, I was told that the query of two tables was the way to start, but if not, let's start from scratch. I've searched and searched for a way to do this and I'm lost.

[Toyman]

ok good! lets start from scratch. I want to pull up a form and subform. I want the form to have all info from one record and be able to find that record - if it exists already - and be able to create a new record if it doesn't. I also want it to be able to pull up and update related information in the subform.

So, I was told that the query of two tables was the way to start, but if not, let's start from scratch. I've searched and searched for a way to do this and I'm lost.

What is the criteria you are using to pull up records; what are you searching by?

[Phoenyxsgirl]

I'm searching by the primary key, the field is called AppID. In the form, I have it as a combo box.

I found this code for the AfterUpdate field on a report so that I can pull up the record relating to the Primary Key that was entered.


Here is the link:
http://access.mvps.org/access/forms/frm0005.htm

here is the code:

'****** Code Start *********
'Move to the record selected in the control
Me.RecordsetClone.Findfirst "[ID] = " & Me![ComboOrListboxName]
Me.Bookmark = Me.RecordSetClone.Bookmark
'******* Code End *********


Unfortunately I'm still such a beginner when it comes to code, I don't know how to translate this to my database.


Does this look like it will work?

If so, the report is based on the table Participants. The primary key field is AppID. AppID is a Combo box.

I want to enter in the AppID and if it matches a record, pull up that record. If it doesn't match, allow a new record to be created.

[Toyman]

I'm searching by the primary key, the field is called AppID. In the form, I have it as a combo box.

I found this code for the AfterUpdate field on a report so that I can pull up the record relating to the Primary Key that was entered.


Here is the link:
http://access.mvps.org/access/forms/frm0005.htm

here is the code:

'****** Code Start *********
'Move to the record selected in the control
Me.RecordsetClone.Findfirst "[ID] = " & Me![ComboOrListboxName]
Me.Bookmark = Me.RecordSetClone.Bookmark
'******* Code End *********


Unfortunately I'm still such a beginner when it comes to code, I don't know how to translate this to my database.


Does this look like it will work?

If so, the report is based on the table Participants. The primary key field is AppID. AppID is a Combo box.

I want to enter in the AppID and if it matches a record, pull up that record. If it doesn't match, allow a new record to be created.

Goto the property setting for your combo box. In the line to set the "After Update" event, click on the drop down arrow to the right and select, "Event Procedure". The Microsoft Visue Basic window will open with the following lines:
Private Sub YourComboBoxName_AfterUpdate()

End Sub

Put the following code in between the two lines.

Dim sAppID As String
sAppID = Me.YourComboBoxName
Me.AppID.SetFocus
DoCmd.FindRecord sAppID, acAnywhere, , acSearchAll

You need to replace "YourComboBoxName" with the name of the combo box you are using for the search. Also, make sure the combo box in not bound to any fields.

What the code does is after you make a selection in the combo box, the AppID value of the combo box is passed on to a veriable (sAppid). The next line sets the focus of the cursor to the AppID field in your form. The last line then search all record in the AppID field to locate the record that matches the veriable, sAppID

[Phoenyxsgirl]

Ok, so the last line keeps saying its bugged.

Dim sAppID As String
sAppID = Me.AppID
Me.AppID.SetFocus
DoCmd.FindRecord sAppID, acAnywhere, , acSearchAll

When I try and alter it to get the help cues to come up, I get this "formula" pop up that lists ....

FindRecord(FindWhat, [Match As AcFindMatch = acEntire], [MatchCase], [Search As AcSearchDirection = acSearchAll], [SearchAsFormatted], [OnlyCurrentField As AcFindField = acCurrent], [FindFirst])

in my terms, I'm guessing this would equal the following:

Private Sub AppID_AfterUpdate()
Dim sAppID As String
sAppID = Participants.AppID
Participants.AppID.SetFocus
DoCmd.FindRecord(sAppID, acEntire, True, acSearchAll, True, acCurrent, True)
End Sub

When I searched help for it, I came up with this last line..

DoCmd.FindRecord(sAppID, acEntire, True, acSearchAll, True, acCurrent, True)
Since the acEntire, acSearchAll, and acCurrent are default values, I'm assuming that they can be left out completely. Its still not working though.

I am already feeling so stupid on this so thank you for not furthering that You've been very kind and patient.

IN the Me.AppID --- does Me get replaced by the name of the table that is being used for the report?

I can at least READ the code now and understand it more but I still don't know enough to fix it.

[Toyman]

Ok, so the last line keeps saying its bugged.

Dim sAppID As String
sAppID = Me.AppID
Me.AppID.SetFocus
DoCmd.FindRecord sAppID, acAnywhere, , acSearchAll

When I try and alter it to get the help cues to come up, I get this "formula" pop up that lists ....

FindRecord(FindWhat, [Match As AcFindMatch = acEntire], [MatchCase], [Search As AcSearchDirection = acSearchAll], [SearchAsFormatted], [OnlyCurrentField As AcFindField = acCurrent], [FindFirst])

in my terms, I'm guessing this would equal the following:

Private Sub AppID_AfterUpdate()
Dim sAppID As String
sAppID = Participants.AppID
Participants.AppID.SetFocus
DoCmd.FindRecord(sAppID, acEntire, True, acSearchAll, True, acCurrent, True)
End Sub

When I searched help for it, I came up with this last line..

DoCmd.FindRecord(sAppID, acEntire, True, acSearchAll, True, acCurrent, True)
Since the acEntire, acSearchAll, and acCurrent are default values, I'm assuming that they can be left out completely. Its still not working though.

I am already feeling so stupid on this so thank you for not furthering that You've been very kind and patient.

IN the Me.AppID --- does Me get replaced by the name of the table that is being used for the report?

I can at least READ the code now and understand it more but I still don't know enough to fix it.

You place the code in the after update event for your actual "AppID" field. This is incorrect. Create a combox box and bound the rowsource to a query that pulls all the AppID. This combo box should not be bound to any control source. Put the code in the after update event of this combo box.

[Phoenyxsgirl]

Ok. When I first started this, I simply changed the box from a text box to a combo box. When I went to create a combo box, the wizard actually created a box for me that looks up the records. This is cheating and not fair LOL especially since I feel like I've wasted your time but I am still trying to learn what went wrong. BTW< it used a macro to search for the record and its not allowing me to enter new records.

So, the difference in what I should have done versus what I did was where I did it, right? I did it in the field when I needed to do it in the combo box itself.

So, I created a combo box. Unbound. Entered a query that simply finds the AppID from the Participants table. Chose [Event Procedure] from the drop down in the AfterUpdate field of the Property Sheet. I entered this information in the code:

Private Sub Combo15_AfterUpdate()
Dim sAppID As String
sAppID = Participants.AppID
Participants.AppID.SetFocus
DoCmd.FindRecord(sAppID, acEntire, True, acSearchAll, True, acCurrent, True)
End Sub

Now its saying the first line is a Syntax Error.