Hello, this is my first post. hopefully i can finally get some hkpe. Ive searched high and low for an answer via google, forums, youtube, you name it ive tried it. Anyhow let me give some background first. I have no previous VBA experience and all that I have learned has been int he past week or so. I put togetehr a pretty decent looking form consodering ,y lack of expereince. I just cant seem to get the update button to work. At a minimum id evebn settle for just a delete button. Just so that if a PM wants to edit a record they can edit it and create a new one based on those chnages and then just go back and delete the original one.
I am creating a project intake form so that PMs can enter all of their projects and it gets saved into a table:
The first sheet contains the form,
the second is the formatted report output,
the third contains the <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">projectdata</code> table where all the records are stored,
the fourth contains lookup lists for the dropdowns,
the last contains various formulas used to navigate the form.
I have successfully added the following navigation buttons: <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">add project</code>, <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">previous record</code>, <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">next record</code>, <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">clear form</code> and <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">close form</code>.
I added the <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">update record</code> button but I can't get the following code to work. What i mean by work is i want to be able to use the navigation buttons to select a record (aka a project) then modify one or more of the fields in the record. I have modified it for the fields that I am using but keep getting an error message and the code gets highlighted.
another note: I can go in and change the record but there is no command button to get those changes to take place in that exact record i want. If i click the <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">add project</code> button it will create an entirely new record from the record I am currently on. Just can't figure out how to edit an existing record without having to create a new one. Hope this makes sense.
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">
Dim fname AsString, lname AsString
pname = Control1.Text
Cells(Currentrow,1).Value = pname
pdesc = Control2.Text
Cells(Currentrow,2).Value = lname
</code>The error message I receive is:
The following piece of code is highlighted yellow, indicating it is incorrect:
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">Cells(Currentrow,1).Value = pname
</code>I have also added the following line at the top to declare the <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">currentrow</code>:
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">Dim Currentrow AsLong
</code>Not sure if this helps, but below is the code I used for the other navigation buttons:
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">PrivateSub CmdNext_Click()
Dim n AsInteger
If Sheets("MiscFormulas").Range("B4")< Sheets("MiscFormulas").Range("B5")Then
n = Sheets("MiscFormulas").Range("B4")+1
Sheets("MiscFormulas").Range("B4")= n
Call update
Else
MsgBox ("You are at the last record!")
EndIf
EndSub
PrivateSub cmdPrevious_Click()
Dim n AsInteger
If Sheets("MiscFormulas").Range("B4")>1Then
n = Sheets("MiscFormulas").Range("B4")-1
Sheets("MiscFormulas").Range("B4")= n
Call update
Else
MsgBox ("You are at the first record!")
EndIf
EndSub
</code>I didn't go ahead and code all the fields for the update button because there are 61 different fields in this form. I only tried the first two to see if it worked before I went in and coded the other 59.
I'm also not sure why the fields had to be defined in the code like why I had manually to say <code style="font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; margin: 0px; padding: 1px 5px; border: 0px; border-image-source: initial; border-image-slice: initial; border-image-width: initial; border-image-outset: initial; border-image-repeat: initial; font-size: 13px; white-space: pre-wrap; background-color: rgb(238, 238, 238);">pname = control1.text</code> instead of just referring to the field as "control1.text". It creates more work to add that extra line for all 61 fields.
BTW, I got this code from another site but couldn't get it to work. I wish I could upload the excel file so you can see but alas hopefully i dont need to. Thanks in advance for all your help. Hope I was thorough enough.
I am creating a project intake form so that PMs can enter all of their projects and it gets saved into a table:
The first sheet contains the form,
the second is the formatted report output,
the third contains the <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">projectdata</code> table where all the records are stored,
the fourth contains lookup lists for the dropdowns,
the last contains various formulas used to navigate the form.
I have successfully added the following navigation buttons: <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">add project</code>, <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">previous record</code>, <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">next record</code>, <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">clear form</code> and <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">close form</code>.
I added the <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">update record</code> button but I can't get the following code to work. What i mean by work is i want to be able to use the navigation buttons to select a record (aka a project) then modify one or more of the fields in the record. I have modified it for the fields that I am using but keep getting an error message and the code gets highlighted.
another note: I can go in and change the record but there is no command button to get those changes to take place in that exact record i want. If i click the <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">add project</code> button it will create an entirely new record from the record I am currently on. Just can't figure out how to edit an existing record without having to create a new one. Hope this makes sense.
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">
Dim fname AsString, lname AsString
pname = Control1.Text
Cells(Currentrow,1).Value = pname
pdesc = Control2.Text
Cells(Currentrow,2).Value = lname
</code>The error message I receive is:
Run Time Error '1004' Application-defined or object-defined error
The following piece of code is highlighted yellow, indicating it is incorrect:
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">Cells(Currentrow,1).Value = pname
</code>I have also added the following line at the top to declare the <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">currentrow</code>:
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">Dim Currentrow AsLong
</code>Not sure if this helps, but below is the code I used for the other navigation buttons:
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">PrivateSub CmdNext_Click()
Dim n AsInteger
If Sheets("MiscFormulas").Range("B4")< Sheets("MiscFormulas").Range("B5")Then
n = Sheets("MiscFormulas").Range("B4")+1
Sheets("MiscFormulas").Range("B4")= n
Call update
Else
MsgBox ("You are at the last record!")
EndIf
EndSub
PrivateSub cmdPrevious_Click()
Dim n AsInteger
If Sheets("MiscFormulas").Range("B4")>1Then
n = Sheets("MiscFormulas").Range("B4")-1
Sheets("MiscFormulas").Range("B4")= n
Call update
Else
MsgBox ("You are at the first record!")
EndIf
EndSub
</code>I didn't go ahead and code all the fields for the update button because there are 61 different fields in this form. I only tried the first two to see if it worked before I went in and coded the other 59.
I'm also not sure why the fields had to be defined in the code like why I had manually to say <code style="font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; margin: 0px; padding: 1px 5px; border: 0px; border-image-source: initial; border-image-slice: initial; border-image-width: initial; border-image-outset: initial; border-image-repeat: initial; font-size: 13px; white-space: pre-wrap; background-color: rgb(238, 238, 238);">pname = control1.text</code> instead of just referring to the field as "control1.text". It creates more work to add that extra line for all 61 fields.
BTW, I got this code from another site but couldn't get it to work. I wish I could upload the excel file so you can see but alas hopefully i dont need to. Thanks in advance for all your help. Hope I was thorough enough.
